SQL Fundamentals: Queries, Joins and Aggregations

SQL looks elemental until an question and reply query returns doubled revenue, missing users, aliases a incorrect apical 3 list. Most failures are not syntax failures - they travel from misunderstanding execution order, JOIN duplicates, aggregation granularity, NULL handling, and model frames. This instruction frames SQL fundamentals done the nonstop reddish flags that costs candidates question and reply rounds, truthful you tin logic earlier you write.

• SQL execution bid is not the aforesaid arsenic constitute order: the database starts pinch FROM and JOIN, past WHERE, GROUP BY, HAVING, SELECT, ORDER BY, and LIMIT aliases TOP.
• Never usage DISTINCT arsenic a unsighted hole for duplicates: it tin hide a many-to-many JOIN rumor and nutrient incorrect counts.
• Always specify granularity earlier GROUP BY: ask, "One statement represents 1 what?" earlier selecting columns aliases aggregates.
• WHERE filters rows, HAVING filters groups: utilizing the incorrect clause tin either correction retired aliases alteration the business meaning of the query.
• COUNT(*) and COUNT(column) are different: COUNT(*) counts each rows including NULLs, while COUNT(column) counts only non-NULL values.
• Window frames request precision: ROWS counts beingness rows, while RANGE groups ties and tin return unexpected results.
• Interview-ready SQL is not conscionable querying: beardown candidates besides explicate metric consistency, information value tests, and why the consequence is correct.

The Big Picture: How SQL Interview Traps Fit Together

SQL question and reply traps usually travel the aforesaid chain: the incorrect root rows are joined, past the incorrect rows are filtered, past the incorrect grouping level is chosen, and yet the incorrect calculation is displayed. The safest attack is to logic successful execution bid earlier penning aliases debugging the query.

SQL, aliases Structured Query Language, is utilized to query system relational tables; successful question and reply answers, explicate the statement grain, JOIN logic, filters, aggregation, and last calculation successful that order.

Why SQL Fundamentals Still Matter

A relational database stores information successful system tables pinch rows, besides called records, and columns, besides called attributes. Tables are linked utilizing keys: a Primary Key, aliases PK, uniquely identifies each row, specified arsenic customer_id, while a Foreign Key, aliases FK, links to a PK successful different table, specified arsenic orders.customer_id linking to customers.customer_id.

SQL remains cardinal because analytical activity depends connected extracting trustworthy facts from system data. The root notes that SQL was created astatine IBM successful the 1970s by Edgar Codd and Don Chamberlin, and still remains the number 1 most-used instrumentality successful information analytics globally successful 2025, up of Python, R, and each BI tool.

The database discourse besides matters. An RDBMS, aliases Relational Database Management System, specified arsenic MySQL, PostgreSQL, Oracle, aliases SQL Server, is typically utilized for transactional systems, system data, and beardown consistency. In the Indian discourse from the source, Razorpay payments DB and HDFC Core Banking are examples of RDBMS usage cases.

Relational Foundations: Keys, Normalisation, and Grain

Before penning joins aliases aggregations, you request to understand what 1 statement intends successful each table. If a customers array has 1 statement per customer and an orders array has galore rows per customer, joining them will people repetition customer columns for each order. That repetition is not automatically wrong, but it becomes incorrect if you aggregate astatine the customer level without accounting for the order-level grain.

Normalisation is the process of organising information to trim redundancy. The root lists the progression arsenic 1NF for atomic values, 2NF for nary partial dependency, and 3NF for nary transitive dependency. In analytics, however, highly normalised structures tin require much JOINs, which is why prima schemas are often utilized for BI queries.

SQL Execution Order: The First Interview Trap

SQL is written successful 1 bid but executed successful another. This is why candidates often effort to usage a SELECT othername successful WHERE, select connected a model usability othername excessively early, aliases wonderment why a GROUP BY query rejects a non-aggregated column.

The root identifies execution bid arsenic a favourite question and reply trap. The database first builds the moving dataset from FROM and JOIN, past filters rows pinch WHERE, collapses rows pinch GROUP BY, filters groups pinch HAVING, computes SELECT expressions and aliases, sorts pinch ORDER BY, and yet returns rows utilizing LIMIT aliases TOP.

Joins: The Duplicate Row Trap

A JOIN combines rows from tables based connected a condition. The trap is that a correct-looking JOIN tin still alteration the number of rows dramatically. If 1 customer has galore orders, joining customers to orders will create aggregate rows for that customer; if some sides person repeated keys, the consequence tin go a many-to-many multiplication.

Using DISTINCT to region the visible duplicates is 1 of the awesome SQL reddish flags successful the source. DISTINCT tin disguise the JOIN issue, hide information value problems, and nutrient incorrect counts. A stronger reply is to diagnose the JOIN condition, array keys, and array granularity.

Aggregations: Granularity, WHERE, HAVING, and NULLs

Aggregation turns galore rows into less rows utilizing functions specified arsenic COUNT, SUM, and AVG. The correct GROUP BY depends connected the business atom of the output. If the desired output is 1 statement per customer, grouping by customer_id is natural; if the desired output is 1 statement per cohort period and months_since_signup, the cohort study query groups by those positions.

WHERE and HAVING are often confused because some select data, but they select astatine different stages. WHERE filters earthy rows earlier aggregation, while HAVING filters groups aft aggregation and tin reference aggregate functions.

Before penning GROUP BY, state: input grain, output grain, row-level filters, grouping columns, aggregate functions, NULL treatment, and last group-level filters.

Window Functions: Ranking, Running Totals, and Frame Mistakes

A model usability calculates crossed a related group of rows without collapsing the consequence into less rows. The root examples see cumulative gross complete clip for Flipkart regular sales, apical 3 products by gross wrong each class for Myntra, year-over-year gross maturation for Nykaa quarterly analysis, and chimney conversion for a Razorpay checkout flow.

The important question and reply nuance is that model aliases are created aft WHERE, truthful you cannot select straight connected a model othername successful the aforesaid SELECT block. The root shows this successful the apical 3 products example: the correct attack is to cipher RANK successful a CTE, aliases Common Table Expression, and past select the classed consequence outside.

Window frames are different reddish flag. ROWS counts beingness rows, while RANGE groups ties and tin see unexpected copy rows pinch the aforesaid value. For a 7-day rolling average, the root uses ROWS BETWEEN 6 PRECEDING AND CURRENT ROW to make the framework explicit.

Worked Example: Debugging a Razorpay Checkout Funnel

Consider a Razorpay checkout chimney query wherever the extremity is to show users astatine each stage, percent of the apical stage, and step-level conversion. The problem is not conscionable penning COUNT(DISTINCT user_id); it is ensuring that the shape order, grouping level, and model calculations each lucifer the chimney definition.

This worked illustration is interview-relevant because it shows reasoning, not memorisation. You explicate the situation, specify the grain, take the grouping columns, usage the correct model functions, and authorities what the output represents.

SQL for Analytics: Patterns Interviewers Expect

The root lists 10 must-know SQL patterns, and respective of them are nonstop extensions of the fundamentals above. Running totals and rolling averages require model frames; rank wrong group requires PARTITION BY and a CTE for filtering; cohort study requires correct grouping; RFM segmentation requires aggregating recency, frequency, and monetary worth earlier scoring pinch NTILE.

RFM intends Recency, Frequency, and Monetary value. In the root example, recency_days is calculated from the latest order_date, wave counts chopped order_id, and monetary_value sums order_amount for completed orders. The scores are past assigned utilizing NTILE(5), and customers are labelled Champion, Loyal, Promising, At Risk, aliases Lost based connected people combinations.

NULL handling besides appears arsenic a applicable analytics pattern. The customer_data illustration uses COALESCE to prime phone_number, email, aliases No interaction info; IFNULL to dainty promo_discount arsenic 0; NULLIF to debar section by zero; COUNT(*) for total_records; and COUNT(phone_number) for records_with_phone.

Database Context: OLTP, OLAP, and Analytics Engineering

SQL questions sometimes grow beyond a azygous query into wherever the query runs and whether the metric is consistent. OLTP, aliases Online Transactional Processing, records day-to-day business transactions utilizing write-heavy operations specified arsenic INSERT, UPDATE, and DELETE. OLAP, aliases Online Analytical Processing, analyses ample volumes of humanities information utilizing read-heavy SELECT queries pinch analyzable aggregations.

Analytics engineering adds different layer. The root notes that successful Flipkart, Razorpay, and Zomato, the Data Analyst domiciled has evolved into Analytics Engineering, wherever the anticipation is not conscionable querying information but ensuring that a metric specified arsenic Revenue intends the aforesaid point successful Tableau, Python, and the information warehouse. Tools and practices specified arsenic dbt, metrics layers, information contracts, information lineage, and information testing support this consistency.

Structuring a SQL Fundamentals Interview Answer

"You joined 2 tables and your gross doubled. How would you debug the SQL query and make judge the last aggregation is correct?"

The number 1 measurement candidates get this incorrect is by jumping consecutive into syntax. Interviewers reward the campaigner who first explains grain, JOIN cardinality, execution order, and metric meaning earlier penning the last query.

The astir predominant correction is utilizing DISTINCT to region copy rows aft a JOIN without investigating why the duplicates appeared. It costs points because it hides the existent rumor - usually array granularity, a one-to-many relationship, aliases a many-to-many JOIN - and tin still time off the last metric wrong!

Conclusion

SQL fundamentals are little astir memorising clauses and much astir protecting business meaning done execution order, correct JOINs, clear aggregation grain, definitive NULL handling, and observant model frames. In interviews, the strongest reply is the 1 that proves the query is logically correct earlier claiming the consequence is correct.