TheShiraverseSELECT * TheShiraverseCurriculumPracticeKahaniFAQGet StartedPlaygroundNukteTheShiraverse ↗
Practice › Topic 16

Window Functions Part 1: practice questions

400 questions in four levels, all on RetailMart, the practice database of this course. Write every query yourself, get it wrong, read the error, fix it. That is how it sticks.

Open the SQL PlaygroundPut RetailMart on your laptop
Answers are coming soon. Post your query in the WhatsApp Community or Discord and we will check it together.

Core syntax, applied directly

CONCEPTUAL

  1. Q1What does a window function do that GROUP BY does not?
  2. Q2Explain the OVER() clause - what does an empty OVER() mean?
  3. Q3What does PARTITION BY do inside OVER()?
  4. Q4What does ORDER BY inside OVER() control for ranking functions?
  5. Q5Define ROW_NUMBER() - is it ever tied?
  6. Q6Define RANK() - what happens to the number after a tie?
  7. Q7Define DENSE_RANK() - how does it differ from RANK() after ties?
  8. Q8Give a one-line summary of ROW_NUMBER vs RANK vs DENSE_RANK on tied values.
  9. Q9What does NTILE(4) do?
  10. Q10Why must a ranking window function have an ORDER BY inside OVER()?
  11. Q11Can you use a window function in a WHERE clause directly? Why not?
  12. Q12What is the usual workaround to filter on a window result (e.g. rn = 1)?
  13. Q13Do window functions remove rows like GROUP BY does?
  14. Q14What is a "partition" vs a "group" conceptually?
  15. Q15After ROW_NUMBER() OVER (ORDER BY x), what is the first row's number?
  16. Q16How do NULLs in the window ORDER BY sort by default (ASC)?
  17. Q17What does PARTITION BY region, category mean (two-level partition)?
  18. Q18Is ROW_NUMBER deterministic when the ORDER BY has ties? How to fix?
  19. Q19What is the "Top-N per group" pattern in one sentence?
  20. Q20Why is ROW_NUMBER preferred over RANK for deduplication?
  21. Q21What does NTILE return when rows don't divide evenly into buckets?
  22. Q22Can the same query have two window functions with different OVER() clauses?
  23. Q23Conceptually, in what order does a window function run vs WHERE and GROUP BY?
  24. Q24What's the difference between RANK() and ROW_NUMBER() output ranges?
  25. Q25Name one analyst use-case each for ROW_NUMBER, RANK, and NTILE.

ROW_NUMBER

  1. Q26Number all orders by order_date (newest first) with ROW_NUMBER.
  2. Q27Number products by price descending.
  3. Q28Number customers by registration_date ascending.
  4. Q29Assign a row number to employees ordered by salary descending.
  5. Q30Number orders within each customer by order_date (PARTITION BY cust_id).
  6. Q31Number products within each brand by price descending.
  7. Q32Number reviews within each product by review_date descending.
  8. Q33Number employees within each store by salary descending.
  9. Q34Get the single most recent order per customer (rn = 1 pattern).
  10. Q35Get the cheapest product per brand (ROW_NUMBER, rn = 1).
  11. Q36Get the latest review per customer.
  12. Q37Number tickets within each priority by created_date.
  13. Q38Number stores within each region by square_ft descending.
  14. Q39Number calls within each customer by call_start_time.
  15. Q40Add a global row number to a SELECT of the 50 highest-value orders.
  16. Q41Number payments within each order by payment_date.
  17. Q42Number shipments within each courier by shipped_date.
  18. Q43Get the first (earliest) order per store using ROW_NUMBER.
  19. Q44Number addresses within each customer (is_default first).
  20. Q45Number ads_spend rows within each platform by amount descending.
  21. Q46Use ROW_NUMBER to label "1st, 2nd, 3rd..." purchase per customer.
  22. Q47Number products within each supplier by product_name.
  23. Q48Get the highest-paid employee per department (rn = 1).
  24. Q49Number orders per store by net_total descending.
  25. Q50Deduplicate customers by email, numbering duplicates by registration_date desc.

RANK / DENSE_RANK

  1. Q51RANK products by price descending (whole table).
  2. Q52DENSE_RANK products by price descending; compare to Q51's gaps.
  3. Q53RANK employees by salary within each department.
  4. Q54DENSE_RANK orders by net_total within each customer.
  5. Q55RANK stores by total square_ft within each region.
  6. Q56Show ROW_NUMBER, RANK, and DENSE_RANK side by side for products by price.
  7. Q57RANK customers by tier then registration_date.
  8. Q58DENSE_RANK products within each brand by price.
  9. Q59RANK reviews by rating within each product (ties expected).
  10. Q60Find the rank of a specific product's price among all products.
  11. Q61RANK ticket priorities by created_date within each status.
  12. Q62DENSE_RANK employees by salary (whole company) - count distinct salary levels.
  13. Q63RANK orders by order_date within each store.
  14. Q64DENSE_RANK brands by number of products (rank a pre-aggregated set).
  15. Q65RANK calls by duration within each agent.
  16. Q66Show why RANK skips numbers after a tie using rating data.
  17. Q67RANK products by cost_price within each category (via brand->category).
  18. Q68DENSE_RANK customers by points_balance (loyalty.members).
  19. Q69RANK shipments by delivery time (delivered_date - shipped_date) within courier.
  20. Q70RANK pay_slips by net_salary within each salary_year.
  21. Q71Find products tied at the same RANK by price within a brand.
  22. Q72DENSE_RANK order statuses by frequency.
  23. Q73RANK regions by store count.
  24. Q74RANK employees by salary descending and keep only rank <= 3 per department.
  25. Q75Compare RANK vs DENSE_RANK on customer tiers (lots of ties).

NTILE & TOP-N

  1. Q76NTILE(4) to split products into 4 price quartiles.
  2. Q77NTILE(10) to split customers into spend deciles (join orders).
  3. Q78NTILE(3) to split employees into low/mid/high salary bands.
  4. Q79Label NTILE(4) buckets as Q1..Q4 with a CASE.
  5. Q80NTILE(5) on order net_total; show the boundary values per bucket.
  6. Q81Top-3 highest-paid employees per department (ROW_NUMBER <= 3).
  7. Q82Top-3 products by price per brand.
  8. Q83Top-2 most recent orders per customer.
  9. Q84Top-5 stores by revenue per region (rank pre-aggregated revenue).
  10. Q85Bottom-3 products by price per brand (ascending rank).
  11. Q86NTILE(4) of customers by registration_date (signup cohorts by quartile).
  12. Q87Second-highest-priced product overall (rn = 2).
  13. Q88Second-highest salary per department.
  14. Q89NTILE(100) to approximate percentile rank of order values.
  15. Q90Top-1 review (highest rating, latest) per product.
  16. Q91NTILE(4) of products by cost_price within each brand.
  17. Q92Top-3 longest calls per agent.
  18. Q93Top-N with a deterministic tie-break (ROW_NUMBER vs RANK choice).
  19. Q94NTILE(2) to split orders into "above/below median" halves.
  20. Q95Top-10 customers by points_balance, numbered.
  21. Q96Bucket stores into 4 size tiers with NTILE on square_ft.
  22. Q97Top-3 brands by product count.
  23. Q98NTILE(4) of employees by salary within each store.
  24. Q99Keep only the 3rd-ranked product by price per brand (exactly nth).
  25. Q100Build spend quartile labels per customer (NTILE(4) + CASE) - RFM preview.

Combined ideas, multi-step thinking

CONCEPTUAL

  1. Q1Why can't you reference a window alias in the same SELECT's WHERE? Show the subquery/CTE fix.
  2. Q2How does PARTITION BY a, b differ from PARTITION BY a for ranking?
  3. Q3Why does ROW_NUMBER need a tie-breaker column to be reproducible?
  4. Q4Explain how RANK leaves gaps and DENSE_RANK does not, with a tie example.
  5. Q5When ranking, what does ORDER BY ... DESC NULLS LAST change vs default?
  6. Q6NTILE(4) vs width_bucket - when would an analyst pick each?
  7. Q7Why is "Top-N per group" a window job, not a GROUP BY job?
  8. Q8How do you get exactly the Nth row per group (not top-N)?
  9. Q9Dedup: why ORDER BY in the window decides which duplicate you keep.
  10. Q10Can two window functions share a window via the WINDOW clause? Show the idea.
  11. Q11What happens to NTILE buckets when group size < bucket count?
  12. Q12Why might RANK and ROW_NUMBER give the same result on a UNIQUE column?
  13. Q13Explain "PARTITION BY with no ORDER BY" for a ranking function.
  14. Q14How do you rank descending but break ties by an ascending second key?
  15. Q15Why does filtering rn <= 3 in an outer query implement top-3?
  16. Q16What's the cost difference between ranking all rows vs adding a WHERE first?
  17. Q17How does DISTINCT interact with a window function in the SELECT?
  18. Q18Why is NTILE sensitive to the ORDER BY tie order?
  19. Q19Explain quartile labeling (NTILE(4) -> Q1..Q4) for segmentation.
  20. Q20When is RANK the "right" choice over ROW_NUMBER for business meaning?
  21. Q21How do you rank within a partition but reset for each new partition value?
  22. Q22Why might you PARTITION BY a derived expression (e.g. DATE_TRUNC)?
  23. Q23What does PERCENT_RANK conceptually add over RANK? (preview only)
  24. Q24How do you keep ties together at the top-N boundary (RANK <= N vs ROW_NUMBER <= N)?
  25. Q25Why compute ranking in a CTE before joining to other tables?

RANKING WITHIN PARTITIONS

  1. Q26Rank products by price within each brand, tie-break by product_id.
  2. Q27Rank employees by salary within each store, tie-break by joining_date.
  3. Q28Rank orders by net_total within each store and order_date month.
  4. Q29Rank customers by points_balance within each tier (join loyalty.members).
  5. Q30Rank products by price within each category (products -> brand -> category).
  6. Q31Rank stores by total revenue within each region (CTE-aggregate then rank).
  7. Q32Rank reviews by rating then review_date within each product.
  8. Q33Rank employees by salary within each (store, role).
  9. Q34Rank brands by product count within each category.
  10. Q35Rank agents by resolved-ticket count within each ticket category.
  11. Q36Rank orders within each customer by net_total DESC, order_date DESC.
  12. Q37DENSE_RANK products by cost_price within each supplier.
  13. Q38Rank shipments by delivery-day count within each courier.
  14. Q39Rank pay_slips by net_salary within each (salary_year, salary_month).
  15. Q40Rank customers by order count within each registration-year cohort.
  16. Q41Rank products by review count within each brand (join reviews).
  17. Q42Rank warehouses by total snapshot quantity within each region.
  18. Q43Rank stores by employee count within each region.
  19. Q44Rank order_items by net_amount within each order.
  20. Q45Rank campaigns by total ad spend within each platform (join ads_spend).
  21. Q46Rank customers by lifetime spend within each city (via addresses).
  22. Q47Rank products by units sold within each brand (join order_items).
  23. Q48Rank employees by salary within department; keep ranks 1-5.
  24. Q49Rank tickets by resolution time within each agent.
  25. Q50Rank regions by average order value (aggregate then rank).

TOP-N / NTH PER GROUP

  1. Q51Top-3 highest-paid employees per department (with names).
  2. Q52Top-3 best-selling products per brand by units.
  3. Q53Top-2 most recent orders per customer (full rows).
  4. Q54Top-5 stores by revenue per region.
  5. Q55The 2nd-highest salary per department (exactly nth).
  6. Q56The 3rd-most-recent order per customer.
  7. Q57Top-1 (latest) review per product, with the review text.
  8. Q58Top-3 customers by spend per city.
  9. Q59Top-N with tie inclusion: all products tied for the top price per brand (RANK).
  10. Q60The single highest-value order per store.
  11. Q61Top-3 longest-open tickets per agent.
  12. Q62Top-2 products by margin (price - cost_price) per category.
  13. Q63The earliest (first-ever) order per customer.
  14. Q64Top-3 regions by store count (rank aggregated).
  15. Q65Top-5 most-reviewed products per brand.
  16. Q66The most expensive product per supplier.
  17. Q67Top-3 agents by call volume per call_reason.
  18. Q68Top-2 highest-rated reviews per customer.
  19. Q69The nth order (parametric, e.g. 5th) per customer.
  20. Q70Top-3 warehouses by capacity per region.
  21. Q71Top-1 per group but break ties deterministically (ROW_NUMBER design).
  22. Q72Top-10 orders overall, then number them per store.
  23. Q73Top-3 brands by revenue per category.
  24. Q74The most recent payment per order.
  25. Q75Top-3 customers by review count per registration year.

NTILE BUCKETING & DEDUP

  1. Q76NTILE(4) customers into spend quartiles (join orders, aggregate).
  2. Q77NTILE(10) products into price deciles within each brand.
  3. Q78NTILE(5) orders into net_total quintiles per store.
  4. Q79NTILE(4) employees into salary quartiles per department; label Q1..Q4.
  5. Q80NTILE(3) stores into small/mid/large by square_ft per region.
  6. Q81Dedup customers by email keeping the most recently registered.
  7. Q82Dedup products by product_name (lowercased) keeping the cheapest.
  8. Q83Dedup orders by (cust_id, order_date) keeping the highest net_total.
  9. Q84Dedup addresses keeping the default (is_default) per customer.
  10. Q85NTILE(4) of review ratings per product into quartile buckets.
  11. Q86NTILE(100) percentile bucket of order net_total per region.
  12. Q87Bucket customers into RFM "Monetary" quartiles (NTILE(4) on spend).
  13. Q88Bucket customers into "Frequency" quartiles (NTILE(4) on order count).
  14. Q89Dedup reviews keeping the latest per (customer, product).
  15. Q90NTILE(4) of products by units sold within category.
  16. Q91Dedup near-duplicate suppliers by lowercased trimmed name.
  17. Q92NTILE(5) of employees by salary across the company; show band ranges.
  18. Q93Dedup payments keeping the latest per order.
  19. Q94NTILE(4) of stores by revenue; tag the top quartile.
  20. Q95Dedup customers by phone (digits only) keeping the latest.
  21. Q96NTILE(3) tenure buckets of employees by joining_date per department.
  22. Q97Combine: top quartile (NTILE(4)=1) customers by spend, then rank within it.
  23. Q98Dedup order_items keeping the line with the largest net_amount per (order, product).
  24. Q99NTILE(4) of products by margin per brand.
  25. Q100RFM bucket preview: NTILE(4) on recency, frequency, monetary separately per customer.

Interview grade, edge cases

CONCEPTUAL

  1. Q1Top-N per group: compare RANK <= N vs ROW_NUMBER <= N when ties exist at the boundary.
  2. Q2Why does ROW_NUMBER give exactly N rows but RANK may give more?
  3. Q3Design a deterministic tie-break for "latest order per customer".
  4. Q4Explain why a window function is evaluated after WHERE/GROUP BY/HAVING but before ORDER BY/LIMIT.
  5. Q5Why must "filter on rank" go in an outer query or CTE (not WHERE)?
  6. Q6Dedup: prove ORDER BY in the partition picks the surviving row.
  7. Q7NTILE vs PERCENT_RANK vs CUME_DIST for "percentile" - which for RFM bucketing?
  8. Q8When group size is not divisible by NTILE(n), which buckets get the extra rows?
  9. Q9Why can ranking the full table then filtering be slower than a LATERAL top-N?
  10. Q10Explain the leftmost-tie problem: equal keys + ROW_NUMBER = arbitrary winner.
  11. Q11How do you compute "rank within group" and "rank overall" in one query?
  12. Q12Why is DISTINCT + window function a common bug?
  13. Q13How to get the top-N AND a count of how many were tied out?
  14. Q14Why PARTITION BY DATE_TRUNC('month', d) for monthly top-N?
  15. Q15Explain "exactly the 2nd highest" with ties (DENSE_RANK = 2).
  16. Q16When should top-N per group be solved with DISTINCT ON instead? (preview)
  17. Q17How does NULLS FIRST/LAST in the window ORDER BY change rank 1?
  18. Q18Why does deduping on a non-unique ORDER BY risk dropping the wrong row?
  19. Q19How to bucket into quartiles but keep bucket edges stable across refreshes?
  20. Q20Explain percentile rank of a value via NTILE(100) and its limitations.
  21. Q21Why might RANK over a huge partition need a supporting sort/index (Topic 20)?
  22. Q22How to rank by a composite score computed in the same query?
  23. Q23Top-N per group across two grain levels (region then store) - how?
  24. Q24Why is "keep latest per email" the canonical dedup interview question?
  25. Q25How to express "everyone in the top decile of spend" cleanly?

TOP-N / NTH PER GROUP

  1. Q26SCENARIO: The CHRO wants the top-3 earners per department with names and salary.
  2. Q27Top-3 products by revenue (units x price) per brand.
  3. Q28The 2nd-highest-salary employee per store (exactly nth, ties via DENSE_RANK).
  4. Q29Top-5 customers by lifetime spend per region.
  5. Q30The latest delivered order per customer (full row, deterministic).
  6. Q31Top-3 most-returned products per category (join returns->order_items->products).
  7. Q32The single highest-margin product per supplier.
  8. Q33Top-3 agents by resolved tickets per ticket category.
  9. Q34The 3rd order ever placed by each customer.
  10. Q35Top-2 stores by revenue per region, with the region total alongside.
  11. Q36Top-N including ties: all products at the max price per brand (RANK = 1).
  12. Q37The most recent review per product, only where rating <= 2 (worst-recent).
  13. Q38Top-3 highest-value orders per payment_mode.
  14. Q39The earliest and latest order per customer in one result (two ROW_NUMBERs).
  15. Q40Top-5 longest-duration calls per agent with caller info.
  16. Q41The top product by units in each (brand, registration-year-of-buyer) - two-level.
  17. Q42Top-3 customers by order count per acquisition month (registration cohort).
  18. Q43The single cheapest in-stock product per warehouse (join snapshots).
  19. Q44Top-3 campaigns by spend per platform with rank shown.
  20. Q45The nth (parametric) most expensive product per category.
  21. Q46Top-2 employees by salary per (store, role) tie-broken by tenure.
  22. Q47The latest payment per order, only for Delivered orders.
  23. Q48Top-3 regions by average order value (aggregate then rank).
  24. Q49The highest-rated, then most-recent, review per product (multi-key order).
  25. Q50Top-N per group then a grand ROW_NUMBER across the survivors.

DEDUPLICATION & IDENTITY

  1. Q51SCENARIO: DBA found duplicate emails - keep the most-recently-registered customer per email.
  2. Q52List the rows that WOULD be deleted by Q51 (rn > 1).
  3. Q53Dedup products by lowercased trimmed product_name, keep the cheapest.
  4. Q54Dedup orders sharing (cust_id, order_date) - keep the highest net_total.
  5. Q55Keep the default address per customer; if none, keep the lowest address_id.
  6. Q56Dedup near-duplicate suppliers by lowercased name; keep the lowest supplier_id.
  7. Q57Dedup reviews per (customer_id, product_id) keeping the latest review_date.
  8. Q58Keep the latest payment per order; flag orders with multiple payments.
  9. Q59Dedup customers by normalized phone (digits only) keeping latest registration.
  10. Q60Identify products appearing under multiple brands (same name) and pick one canonical.
  11. Q61Dedup order_items per (order_id, prod_id) keeping the max net_amount line.
  12. Q62Find emails with >=3 duplicate accounts and rank the keepers.
  13. Q63Keep the most recent snapshot per (warehouse_id, product_id).
  14. Q64Dedup loyalty.members per customer (defensive) keeping highest points_balance.
  15. Q65Produce a "golden record" per email: keep latest, list merged ids.
  16. Q66Dedup tickets per (customer_id, subject) keeping the latest created_date.
  17. Q67Keep the first-ever order per customer as their "acquisition order".
  18. Q68Dedup addresses by (customer_id, pincode) keeping is_default then lowest id.
  19. Q69Find customers whose duplicates span different cities (data-quality flag).
  20. Q70Dedup products by (brand_id, product_name) keeping the most expensive.
  21. Q71Rank duplicate-group sizes: which email has the most duplicate accounts?
  22. Q72Keep the latest review per product and compute its position vs all reviews.
  23. Q73Dedup calls per (customer_id, call_start_time::date) keeping the longest.
  24. Q74Produce a deduped customer list (rn = 1) ready for an export.
  25. Q75Validate dedup: count rows before vs after (rn = 1) per email.

NTILE SEGMENTATION & PERCENTILES

  1. Q76SCENARIO: Marketing wants customers split into spend quartiles (NTILE(4)) labeled Q1..Q4.
  2. Q77NTILE(10) spend deciles per region; show decile boundaries.
  3. Q78RFM "Monetary": NTILE(5) on lifetime spend per customer.
  4. Q79RFM "Frequency": NTILE(5) on order count per customer.
  5. Q80RFM "Recency": NTILE(5) on days-since-last-order per customer.
  6. Q81Combine the three NTILE(5) scores into an RFM code (e.g. '5-4-3').
  7. Q82Tag the top spend quartile customers as 'VIP' and rank within it.
  8. Q83NTILE(4) products by margin per category; list the top-quartile products.
  9. Q84Percentile rank of each order's net_total via NTILE(100) per region.
  10. Q85NTILE(4) employees by salary per department; compare to company-wide quartile.
  11. Q86Bucket stores into 4 revenue tiers; show count and avg per tier.
  12. Q87NTILE(3) of products by units sold (slow/medium/fast movers) per brand.
  13. Q88NTILE(4) of reviews by rating per product and tag the bottom quartile.
  14. Q89Find customers in the top decile of BOTH frequency and monetary.
  15. Q90NTILE(5) of warehouses by total stock; flag the lowest quintile for audit.
  16. Q91Quartile of delivery time per courier (NTILE(4)); slowest quartile per courier.
  17. Q92NTILE(4) of campaigns by spend per platform; top-quartile spenders.
  18. Q93Build a "spend tier x frequency tier" segmentation grid (two NTILEs).
  19. Q94NTILE(10) of order values overall; the 90th-percentile bucket threshold.
  20. Q95Segment customers into quartiles and rank them within each quartile by recency.
  21. Q96NTILE(4) tenure buckets of employees per store; newest-quartile list.
  22. Q97Percentile bucket of product price per brand (NTILE(100)) and its rank.
  23. Q98NTILE(4) of customers by review count; engaged top-quartile.
  24. Q99Compare NTILE(4) bucket vs RANK-based quartile on the same column - when they differ.
  25. Q100Full RFM segmentation: NTILE(5) R/F/M, map codes to Champions/Loyal/At-Risk/Lost.

Production scenarios, optimisation

CONCEPTUAL

  1. Q1Design a stable, reproducible ranking key for paginated leaderboards.
  2. Q2Multiple OVER() clauses in one SELECT - cost model and the WINDOW clause to share them.
  3. Q3Prove that ROW_NUMBER over a tie is non-deterministic without a unique tail key.
  4. Q4Top-N per group: window-filter vs LATERAL vs DISTINCT ON - tradeoffs at scale.
  5. Q5How ties + NTILE interact: why two equal values can land in different buckets.
  6. Q6Why "rank then join" beats "join then rank" for fan-out control.
  7. Q7Compose RANK over a weighted score (e.g. 0.5*norm_spend + 0.3*freq + 0.2*recency).
  8. Q8Percentile semantics: NTILE(100) vs PERCENT_RANK vs CUME_DIST - exact differences.
  9. Q9Deduplication at scale: choosing the partition key + ORDER BY for a golden record.
  10. Q10Why DISTINCT ON is sometimes faster than ROW_NUMBER=1 for latest-per-group.
  11. Q11Ranking within rolling cohorts (PARTITION BY DATE_TRUNC) - pitfalls.
  12. Q12How to guarantee top-N returns <= N even with ties (and when you want >= N).
  13. Q13Multi-level top-N (top-2 stores per region AND top-3 products per store) in one pass.
  14. Q14Why segmentation buckets should be recomputed, not stored, for fairness over time.
  15. Q15Designing RFM with NTILE: edge cases (all-equal frequency, single-order customers).
  16. Q16When ranking on a computed column, why materialize it in a CTE first.
  17. Q17Determinism across runs: collation, NULLS ordering, and tie tails.
  18. Q18How to rank but exclude outliers (trim top/bottom percentile) cleanly.
  19. Q19Top-N per group with a global cap (e.g. <=3 per brand but <=50 overall).
  20. Q20Why a leaderboard needs both DENSE_RANK (display) and ROW_NUMBER (pagination).
  21. Q21NTILE drift: why bucket membership changes as data grows, and mitigation.
  22. Q22Ranking with business tie-break rules encoded as a CASE sort key.
  23. Q23How to compute "share of rank-1" (how dominant the leader is) per group.
  24. Q24Reproducible quartile cut-points: store the NTILE boundaries as a snapshot.
  25. Q25Why a single query with 4 windows can beat 4 self-joins (correctness + speed).

MULTI-WINDOW RANKING

  1. Q26SCENARIO: VP Sales wants each product's rank within its brand AND its rank overall, side by side.
  2. Q27For each employee: salary rank in department, in store, and company-wide (3 windows).
  3. Q28Rank customers by spend within region and within tier in one query.
  4. Q29Composite score leaderboard: weight spend/frequency/recency, then RANK + ROW_NUMBER.
  5. Q30Per product: RANK by price and DENSE_RANK by units, compared.
  6. Q31Two-level top-N: top-2 regions by revenue, and within them top-3 stores.
  7. Q32Per order: line-item rank by net_amount and the order's rank among the customer's orders.
  8. Q33Rank stores by revenue and by order count; flag where the two ranks disagree.
  9. Q34Per customer: rank their orders by value and tag the single largest with a flag.
  10. Q35Use the WINDOW clause to share one PARTITION across ROW_NUMBER, RANK, DENSE_RANK.
  11. Q36Rank brands by revenue within category and the category by revenue overall.
  12. Q37Per agent: rank by tickets resolved and by avg resolution speed; combined score.
  13. Q38Rank products within brand and mark those also in the global top-100 by units.
  14. Q39Per region: rank stores by revenue and by review score; show both.
  15. Q40Build a "dominance" metric: leader's value / 2nd place value per group.
  16. Q41Rank customers by recency and by monetary; keep those top-quartile in both.
  17. Q42Per warehouse: rank products by stock and by turnover (join order_items).
  18. Q43Rank pay_slips by net_salary within year and within (year, month).
  19. Q44Multi-key tie-break encoded via CASE: priority then SLA then created_date.
  20. Q45Per category: top product by units and top product by margin in one result.
  21. Q46Rank campaigns by spend within platform and platform by spend overall.
  22. Q47Per customer: position of their first order's value among all their orders.
  23. Q48Rank cities by customer count and by total spend (via addresses + orders).
  24. Q49Compute each product's percentile (NTILE 100) within brand and overall.
  25. Q50A single query producing brand rank, category rank, and global rank for every product.

DEDUP & TOP-N AT SCALE

  1. Q51SCENARIO: Build a deduped "golden customer" table - one row per email, latest record, merged id list.
  2. Q52Dedup at scale keeping latest per email; report total rows removed.
  3. Q53Golden product record per lowercased name with surviving brand chosen by max units.
  4. Q54Latest order per customer for 50k customers - compare ROW_NUMBER=1 vs DISTINCT ON.
  5. Q55Top-3 products per brand across the full catalog, deterministic.
  6. Q56Reconcile two near-duplicate supplier spellings into one canonical set.
  7. Q57Dedup reviews keeping the highest-rated, then latest, per (customer, product).
  8. Q58Top-N per group with a global cap of 100 survivors total.
  9. Q59Find and rank the largest duplicate clusters (emails with most accounts).
  10. Q60Golden address per customer (default -> latest -> lowest id) with audit trail.
  11. Q61Top-5 customers by spend per city, only cities with >=100 customers.
  12. Q62Latest snapshot per (warehouse, product) and rank products by that stock.
  13. Q63Dedup order_items to one line per (order, product) keeping the max amount.
  14. Q64Build the "first touch" order per customer and rank customers by its value.
  15. Q65Top-3 per group but include ties at the boundary (RANK) and count the tie-outs.
  16. Q66Dedup payments per order; for multi-payment orders, keep latest and sum the rest.
  17. Q67Canonical product list: dedup by (brand, name), keep dearest, number duplicates.
  18. Q68Top-N most-returned products per category with return-rate, deterministic.
  19. Q69Identify customers whose duplicates differ in tier (data-integrity flag).
  20. Q70Latest review per product and its rank among that product's ratings.
  21. Q71Top-3 agents per category by resolved count, tie-broken by avg speed.
  22. Q72Produce a deduped export (rn=1) and validate counts per partition.
  23. Q73Top product per (brand, buyer-tier) - two-level, full catalog.
  24. Q74Dedup customers by phone digits; resolve conflicting names by latest registration.
  25. Q75Golden-record pipeline: rank, keep rn=1, collect merged ids via string_agg.

SEGMENTATION SYSTEMS

  1. Q76SCENARIO: Build a full RFM segmentation: NTILE(5) on Recency, Frequency, Monetary per customer.
  2. Q77Map RFM codes to segments: Champions / Loyal / Potential / At-Risk / Lost.
  3. Q78Customer spend deciles (NTILE(10)) with decile revenue contribution (Pareto check).
  4. Q79Two-dimensional grid: spend quartile x frequency quartile, count per cell.
  5. Q80Product ABC classification via NTILE on revenue contribution per category.
  6. Q81Store performance quartiles per region with quartile averages.
  7. Q82Percentile rank (NTILE 100) of every customer's spend; flag >= p95.
  8. Q83RFM but robust to single-order customers (handle degenerate NTILE).
  9. Q84Top-decile-by-spend AND top-decile-by-frequency overlap (the "best" cohort).
  10. Q85Quartile of delivery time per courier; SLA-risk = bottom quartile.
  11. Q86Employee comp quartiles per department vs company; flag underpaid top performers.
  12. Q87Bucket products into fast/medium/slow movers (NTILE 3 on units) per brand.
  13. Q88Segment campaigns into spend quartiles per platform; ROI-watch the top quartile.
  14. Q89Customer "engagement quartile" from review+ticket+order counts (composite NTILE).
  15. Q90Region revenue quartiles with each region's rank and quartile shown.
  16. Q91Snapshot the NTILE(4) spend cut-points so buckets are reproducible next month.
  17. Q92Cross-segment: which spend quartile dominates each city (mode per group).
  18. Q93Decile migration setup: assign current spend decile per customer (for later compare).
  19. Q94Flag "rising" customers: top frequency quartile but only mid monetary quartile.
  20. Q95Product price-band segmentation (NTILE 5) per brand with band labels.
  21. Q96Warehouse stock quintiles; lowest quintile flagged for replenishment review.
  22. Q97Customer tier vs NTILE-spend-quartile mismatch (Gold tier but bottom spend quartile).
  23. Q98Build an exec "leaderboard strip": top-3 per region with rank + dominance metric.
  24. Q99RFM segment sizes and average monetary per segment (segmentation report).
  25. Q100End-to-end: RFM-score every customer, label segments, and rank customers within each segment.