Advance SQL Concepts

Question 2

What are window functions?

Answer 2

Functions that perform calculations across sets of rows related to the current row without collapsing them.

Question 3

What does ROW_NUMBER() do?

Answer 3

Assigns a unique sequential integer to rows within a partition.

Question 4

What does RANK() do?

Answer 4

Gives ranks with gaps for ties in ordered results.

Question 5

What does DENSE_RANK() do?

Answer 5

Gives ranks without gaps for ties.

Question 6

What clause defines partitions for window functions?

Answer 6

PARTITION BY inside the OVER() clause.

Question 7

What clause defines order for window functions?

Answer 7

ORDER BY inside the OVER() clause.

Question 8

What is the difference between GROUP BY and window functions?

Answer 8

GROUP BY collapses rows into one per group, window functions keep individual rows while computing aggregates.

Question 9

What is a common table expression (CTE)?

Answer 9

A temporary result set defined using WITH that can be referenced within the main query.

Question 10

What is the difference between a CTE and a subquery?

Answer 10

CTEs can be referenced multiple times and improve readability; subqueries cannot.

Question 11

How do you calculate cumulative sums using window functions?

Answer 11

SUM(column) OVER(ORDER BY column) computes cumulative totals across rows.

Question 12

How do you find the previous row’s value in a window?

Answer 12

Use LAG(column) OVER(ORDER BY column) to access the previous row value.

Question 13

How do you find the next row’s value in a window?

Answer 13

Use LEAD(column) OVER(ORDER BY column) to access the next row value.

Question 14

How do you select the top N rows per group?

Answer 14

Use ROW_NUMBER() or RANK() with PARTITION BY to rank rows, then filter on the rank.

Question 15

How do you calculate percentiles or top X% per group?

Answer 15

Use NTILE(n) OVER(PARTITION BY group_column ORDER BY value_column) to divide rows into n buckets.

Question 16

How can you combine multiple CTEs?

Answer 16

Chain them using WITH cte1 AS (…), cte2 AS (…) SELECT … FROM cte2 JOIN cte1 …

Question 17

How do you filter aggregated groups using window functions?

Answer 17

Window functions don’t filter rows directly; use ROW_NUMBER()/RANK in a subquery or CTE and then filter in the outer query.

Question 18

How do you calculate running averages per user?

Answer 18

Use AVG(column) OVER(PARTITION BY user_id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).

Question 19

How do you find gaps or sequences in data?

Answer 19

Use LAG() or LEAD() to compare current row with previous/next rows, then filter differences.

Question 20

How do you handle ties in ranking?

Answer 20

RANK() leaves gaps, DENSE_RANK() does not; choose based on requirement.

Question 21

How do you calculate cumulative counts per group?

Answer 21

COUNT(*) OVER(PARTITION BY group_column ORDER BY sort_column ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).

Question 22

How do you write a recursive CTE?

Answer 22

WITH RECURSIVE cte_name AS (anchor_query UNION ALL recursive_query) SELECT * FROM cte_name;

Question 23

How do you calculate a moving average over last N rows?

Answer 23

AVG(column) OVER(ORDER BY date ROWS BETWEEN N PRECEDING AND CURRENT ROW);

Question 24

How do you join multiple CTEs?

Answer 24

Define them in WITH clause separated by commas, then reference them in the main SELECT with JOINs.

Question 25

How do you implement a top-N-per-category query?

Answer 25

Use ROW_NUMBER() OVER(PARTITION BY category ORDER BY metric DESC) as rank, then filter WHERE rank <= N.

Question 26

How do you calculate cumulative sums per category?

Answer 26

SUM(amount) OVER(PARTITION BY category ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).

Question 27

How do you identify first and last events per group?

Answer 27

Use ROW_NUMBER() OVER(PARTITION BY group_column ORDER BY date ASC) = 1 for first, DESC = 1 for last.

Question 28

How can you rank rows across the entire table vs per group?

Answer 28

Omit PARTITION BY for entire table ranking; include PARTITION BY for per-group ranking.

Question 29

How do you calculate difference between current and previous row?

Answer 29

Use column - LAG(column) OVER(ORDER BY column) to get the difference.

Question 30

How do you filter top X% of rows in SQL?

Answer 30

Use CTE/subquery with RANK()/NTILE() and filter rank <= X% of total count.

Question 32

Return only rows from A that have at least one match in B — what’s this join called?

Answer 32

Left semi-join.

Question 33

Return only rows from A that have no matches in B — what’s this join called?

Answer 33

Left anti-join.

Question 34

Write the most portable SQL pattern for a left semi-join between users(u) and orders(o) on user_id.

Answer 34

SELECT u.* FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id);

Question 35

Write a left semi-join using IN (and name the caveat).

Answer 35

SELECT u.* FROM users u WHERE u.user_id IN (SELECT o.user_id FROM orders o); Caveat: NULLs in the subquery can cause unexpected behavior.

Question 36

Write a left semi-join using LEFT JOIN (and filter) and say when DISTINCT might be needed.

Answer 36

SELECT DISTINCT u.* FROM users u LEFT JOIN orders o ON o.user_id = u.user_id WHERE o.user_id IS NOT NULL; Use DISTINCT if multiple matches in B would duplicate A.

Question 37

Write the preferred SQL pattern for a left anti-join between users(u) and orders(o) on user_id.

Answer 37

SELECT u.* FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id);

Question 38

Write a left anti-join using NOT IN and state the risk.

Answer 38

SELECT u.* FROM users u WHERE u.user_id NOT IN (SELECT o.user_id FROM orders o); Risk: if the subquery returns any NULL, the result can be empty.

Question 39

Write a left anti-join using LEFT JOIN (and filter).

Answer 39

SELECT u.* FROM users u LEFT JOIN orders o ON o.user_id = u.user_id WHERE o.user_id IS NULL;

Question 40

Why prefer EXISTS/NOT EXISTS over IN/NOT IN for semi/anti joins?

Answer 40

Better NULL semantics and typically more optimizer-friendly; avoids NOT IN + NULL trap.

Question 41

If table B has multiple matching rows per A, how many rows from A appear in a semi-join result?

Answer 41

One per A (semi-join acts like a filter on A).

Question 42

You need columns from B in the result—should you use a semi-join?

Answer 42

No. Use an inner/left join and aggregate/limit as needed.

Question 43

Which column should you reference in WHERE o.col IS NULL for an anti-join via LEFT JOIN?

Answer 43

A column from B that participates in the join (to correctly detect “no match”).

Question 44

What index helps most for EXISTS/NOT EXISTS on orders(user_id)?

Answer 44

An index on orders(user_id).

Question 45

Engine-specific: How do you write a semi-join in Spark SQL/PySpark?

Answer 45

dfA.join(dfB, on=..., how="left_semi")

Question 46

Engine-specific: How do you write an anti-join in Spark SQL/PySpark?

Answer 46

dfA.join(dfB, on=..., how="left_anti")

Question 47

SQL dialects (Postgres/MySQL/BigQuery/Snowflake): is there a SEMI JOIN keyword?

Answer 47

No; use EXISTS/NOT EXISTS or LEFT JOIN + (NOT) NULL filters.

Question 48

Interview check: “Return users who placed at least one order last month.” Which pattern?

Answer 48

Left semi-join via EXISTS.

Question 49

Interview check: “Find users who have never ordered.” Which pattern?

Answer 49

Left anti-join via NOT EXISTS.

Question 50

Interview check: “Filter products that appear in a top_sellers table.” Which pattern?

Answer 50

Left semi-join via EXISTS (or IN if no NULLs).

Question 51

Give a minimal EXISTS template you can adapt quickly.

Answer 51

SELECT a.* FROM A a WHERE EXISTS (SELECT 1 FROM B b WHERE b.key = a.key AND );

Question 52

Give a minimal NOT EXISTS template you can adapt quickly.

Answer 52

SELECT a.* FROM A a WHERE NOT EXISTS (SELECT 1 FROM B b WHERE b.key = a.key AND );

Question 53

Explain the NOT IN + NULL pitfall in one line.

Answer 53

If the subquery returns any NULL, x NOT IN (… NULL …) evaluates to UNKNOWN, often yielding zero rows.

Question 54

True/False: Planners often rewrite EXISTS into a semi-join internally.

Answer 54

True.