My progress
Menu

Data Analyst / Engineer · Focus area 3: Analytics and dashboards

DT8: Advanced SQL for analysis

Goal: answer real business questions in SQL that others can read and check: break queries into named steps, use window functions for rankings, running totals and changes, build cohort and funnel analyses, and avoid the traps that make a correct-looking number wrong.

You'll need: DT5, DT6, Module 55 (SQL Fundamentals), Module 56 (Advanced Queries), and the SabziSathi practice kit.

Where this is used: "top three products in each store", "how many customers who joined in October ordered again", "where do people drop out of checkout". Questions like these come up every week in every business with data, and SQL is the common language for answering them.

How it differs: an app runs the same few queries millions of times. An analyst writes new queries every day, so readability, checking and knowing the traps matter more than raw speed.

Time: reading about 2½ hours · practice (the Try it tasks) about 3 hours · assignment about 3 hours.

Lessons

  1. 1Readable queries with CTEsReading 15–25 minutes, plus practice✓
  2. 2Window functionsReading 15–25 minutes, plus practice✓
  3. 3Cohorts and retentionReading 15–25 minutes, plus practice✓
  4. 4Funnels and conversionReading 15–25 minutes, plus practice✓
  5. 5Traps that make numbers wrongReading 15–25 minutes, plus practice✓

🛠️Assignment: Answers for SabziSathi's managers

This assignment is part of Project 3's preparation.

  1. Write, as CTEs, the top three products per store by net revenue, with rank().
  2. Write daily net revenue with the day-on-day change and a 7-day moving average.
  3. Build the weekly cohort table of first delivered orders, as counts and percentages, with your caveats written beside it.
  4. Write one query that falls into a trap from Lesson 5 and its corrected version, with the row counts or totals of both.
  5. Put all four in docs/analysis.md, each with the question, the SQL, the result and one sentence a manager could act on.

Self-check before you finish:

✅Module check

Explain in a few sentences, in your own words:

How do you write analysis SQL that others can trust?

Show a good answer

A good answer mentions: named CTE steps with row checks; window functions for ranks (with ties handled), running totals, moving averages and changes; cohorts by first period with caveats about where data starts and incomplete periods; funnels that count distinct people and require earlier steps; and avoiding fan-out, NULL traps, not in with NULLs, averages of averages, adding distinct counts, timestamp boundaries and integer division.

📚Official references

Official documentation and guidance for this module's topics, chosen on 2 November 2026. Tools, laws and standards change, so check the current version before you rely on a detail. If a link has moved, search the same site.

📝Module quiz

10 questions · 10 marks each · 100 marks in all · pass mark 70% · retry as often as you like ·

1. An analyst needs the top two products per city. Which approach works?

Why each option is right or wrong

  • “group by city with order by sales desc limit 2” (Incorrect): limit applies to the whole result, giving two rows overall, not two per city.
  • “max(sales) per city, run twice” (Incorrect): The second run returns the same maximum.
  • “where rank() over (...) <= 2 in the same query” (Incorrect): Window results can't be filtered in the same where; rank first, then filter.
  • “Rank per city in a CTE, then keep ranks 1 and 2” is correct.

    rank() over (partition by city order by sales desc) numbers products within each city; the next step filters.

    • Step 1: a CTE with the rank per city.
    • Step 2: where rk <= 2.

📖 Revise: Lesson 2: Window functions

Question ID sp-data-08-q1 · Report a mistake in this question
2. A "best dish per outlet" report uses row_number(). Two dishes tie at one outlet, and the winner changes from day to day. What fixes it?

Why each option is right or wrong

  • “Run the report at the same time every day” (Incorrect): The order of tied rows isn't fixed by the clock.
  • “Use lag() to compare with yesterday's winner” (Incorrect): That doesn't resolve ties.
  • “Sort the table by dish name before the query” (Incorrect): Tables have no lasting order for queries to rely on.
  • “Use rank(), or add a tie-breaker to the order by” is correct.

    rank() shows both tied dishes; a tie-breaker (such as dish name) makes row_number() give the same answer every run.

    • Ties shown: rank() gives both dishes rank 1.
    • One winner: row_number() over (... order by sales desc, dish_name).

📖 Revise: Lesson 2: Window functions

Question ID sp-data-08-q2 · Report a mistake in this question
3. A cohort table shows the most recent cohort with the lowest week-4 retention. The month isn't over yet. What should the analyst say?

Why each option is right or wrong

  • “The newest customers are the least loyal” (Incorrect): Their week 4 hasn't fully happened yet.
  • “Retention is falling and needs urgent action” (Incorrect): The figure isn't comparable yet.
  • “The cohort should be merged with an older one to make it bigger” (Incorrect): Merging hides the comparison cohorts exist for.
  • “Week 4 is incomplete, so it can't be compared yet” is correct.

    Recent cohorts have had less time. Mark incomplete periods or leave them out.

    • Say: how many days each period holds.
    • Compare: cohorts at the same age only.

📖 Revise: Lesson 3: Cohorts and retention

Question ID sp-data-08-q3 · Report a mistake in this question
4. Overall monthly retention rose in March, but every cohort's retention fell. How is that possible?

Why each option is right or wrong

  • “The cohort table must have a calculation error” (Incorrect): It can be correct; this pattern is real.
  • “March has more days than February” (Incorrect): Day counts don't explain falling cohorts.
  • “Retention can't rise if every cohort falls” (Incorrect): It can, through the mix of cohorts.
  • “Older, loyal customers became a larger share of the active base” is correct.

    A change in the mix of cohorts can lift the overall figure while each cohort gets worse.

    • Lesson: look cohort by cohort.

📖 Revise: Lesson 3: Cohorts and retention

Question ID sp-data-08-q4 · Report a mistake in this question
5. A funnel shows 10,000 product views, 4,000 add-to-carts and 3,500 payments. Many payments come from a "buy again" button that skips the

Why each option is right or wrong

  • “Nothing, as 3,500 ÷ 4,000 is 87.5%” (Incorrect): The division is right; the people in the two counts aren't the same group.
  • “Conversion should be measured from views, not carts” (Incorrect): Both step and overall conversion are useful; that isn't the error here.
  • “The figure should be rounded to 88%” (Incorrect): Rounding doesn't fix who's counted.
  • “Payments include people who skipped the cart” is correct.

    Each step should count only people who reached the earlier steps.

    • Fix: count a payment only for people who also added to cart.
    • Then: report "buy again" payments separately.

📖 Revise: Lesson 4: Funnels and conversion

Question ID sp-data-08-q5 · Report a mistake in this question
6. A funnel query uses count(*) at each step. One customer added 12 items to the cart. What happens?

Why each option is right or wrong

  • “They count once, because SQL removes repeats automatically” (Incorrect): SQL counts every row unless told otherwise.
  • “They're removed from the funnel for having too many events” (Incorrect): Nothing removes them.
  • “The query fails because of duplicate users” (Incorrect): Duplicates are valid rows.
  • “They count 12 times at the cart step” is correct.

    count(*) counts events. Funnels count people: count(distinct user_id).

    • Fix: count(distinct user_id) at each step.
    • Check: a step can't have more people than the step before.

📖 Revise: Lesson 4: Funnels and conversion

Question ID sp-data-08-q6 · Report a mistake in this question
7. select count(*) from loans where customer_id not in (select customer_id from defaults) returns 0, though most customers never

Why each option is right or wrong

  • “The loans table is empty” (Incorrect): The question says most customers have loans.
  • “not in doesn't work with subqueries” (Incorrect): It works, but with a trap.
  • “count(*) skips rows with NULL values in any column” (Incorrect): count(*) counts rows, NULLs included.
  • “A NULL in defaults makes not in match nothing” is correct.

    Comparing with NULL is unknown, so every row fails. not exists avoids it.

    • Rewrite: where not exists (select 1 from defaults d where d.customer_id = loans.customer_id).
    • Also: a not_null test on defaults.customer_id.

📖 Revise: Lesson 5: Traps that make numbers wrong

Question ID sp-data-08-q7 · Report a mistake in this question
8. After joining order lines to a customer history table, revenue rises by 7%. Nothing else changed. What's the first thing to check?

Why each option is right or wrong

  • “Whether prices went up that month” (Incorrect): The data didn't change; only the query did.
  • “Whether the history table is sorted correctly” (Incorrect): Sort order doesn't change join results.
  • “Whether revenue should be shown in lakh instead of rupees” (Incorrect): Units don't change totals.
  • “Whether the join matched some lines more than once” is correct.

    Several matching rows per key copy lines (fan-out). Compare row counts before and after.

    • Typical cause: a history or price table with several rows per key.
    • Fix: add the missing condition, such as the time range.

📖 Revise: Lesson 5: Traps that make numbers wrong

Question ID sp-data-08-q8 · Report a mistake in this question
9. avg(rating) on a table where some ratings are NULL. What does it return?

Why each option is right or wrong

  • “An average that treats NULLs as 0” (Incorrect): That would lower the average; SQL doesn't do that.
  • “NULL, because any NULL makes the average unknown” (Incorrect): That's how + behaves, not avg.
  • “An error asking you to remove NULLs first” (Incorrect): No error is raised.
  • “The average of the non-NULL ratings only” is correct.

    Aggregates skip NULLs, so avg divides by the number of ratings present. Say how many were missing.

    • Report: how many ratings were missing.
    • If NULL means zero: use coalesce(rating, 0), on purpose.

📖 Revise: Lesson 5: Traps that make numbers wrong

Question ID sp-data-08-q9 · Report a mistake in this question
10. Which query structure is easiest to review?

Why each option is right or wrong

  • “One query with four nested subqueries” (Incorrect): Correct, maybe, but hard to follow and check.
  • “Four queries saved as separate temporary tables by hand” (Incorrect): Repeatable only if someone runs every step in order.
  • “One line of SQL to keep the file short” (Incorrect): Short isn't the same as clear.
  • “Named CTEs, one idea per step, each counted” is correct.

    Reviewers read top to bottom and can check each step's rows.

    • Check: each step's rows match what you expect.
    • Bonus: reviewers can reuse single steps.

📖 Revise: Lesson 1: Readable queries with CTEs

Question ID sp-data-08-q10 · Report a mistake in this question

ProLearners Assist