TheShiraverseSELECT * TheShiraverseCurriculumPracticeKahaniFAQGet StartedPlaygroundNukteTheShiraverse ↗
Practice › Topic 22

Median, Percentiles and DISTINCT ON: 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 the median represent, and how does it differ from the mean (AVG)?
  2. Q2Why is the median more robust to outliers than the average?
  3. Q3What does PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) compute?
  4. Q4What is the WITHIN GROUP (ORDER BY ...) clause for?
  5. Q5Difference between PERCENTILE_CONT (interpolates) and PERCENTILE_DISC (real value)?
  6. Q6What do "P90" and "P95" mean as percentiles?
  7. Q7Why is P95 a common SLA / latency metric instead of the average?
  8. Q8What values do PERCENTILE_CONT(0.0) and PERCENTILE_CONT(1.0) return?
  9. Q9Is PERCENTILE_CONT an aggregate like SUM/AVG? (ordered-set aggregate)
  10. Q10Can PERCENTILE_CONT be combined with GROUP BY for a per-group median?
  11. Q11What does DISTINCT ON (col) do in PostgreSQL?
  12. Q12Why must DISTINCT ON normally be paired with an ORDER BY?
  13. Q13Which row does DISTINCT ON keep per group (first by the ORDER BY)?
  14. Q14How is DISTINCT ON different from GROUP BY?
  15. Q15How is DISTINCT ON different from filtering ROW_NUMBER() = 1 (Topic 16)?
  16. Q16Why is DISTINCT ON a PostgreSQL-specific shortcut?
  17. Q17What does "latest record per group" mean for an analyst (give an example)?
  18. Q18Why does PERCENTILE_CONT interpolate between two neighbouring values?
  19. Q19When would you prefer PERCENTILE_DISC (must return an actual observed value)?
  20. Q20What does the mode() WITHIN GROUP (ORDER BY x) aggregate return?
  21. Q21How do percent_rank() / cume_dist() (Topic 16) relate to percentiles?
  22. Q22How does NTILE(100) (Topic 16) approximate percentile buckets?
  23. Q23Why can median delivery time tell a different story than average delivery time?
  24. Q24Can you pass an array of fractions, e.g. PERCENTILE_CONT(ARRAY[0.5,0.9,0.95])?
  25. Q25Name two business KPIs better expressed as a median than as a mean.

MEDIAN & PERCENTILES

  1. Q26Median net_total across all orders.
  2. Q27Average vs median net_total side by side in one row.
  3. Q28P90 and P95 of net_total across all orders.
  4. Q29Median net_total per region (orders -> stores -> region).
  5. Q30Median net_total per store.
  6. Q31P95 net_total per region.
  7. Q32Median order value per payment_mode.
  8. Q33Median delivery time in days (delivered_date - order_date) for Delivered orders.
  9. Q34P90 delivery time in days across Delivered orders.
  10. Q35Median delivery time in days per region.
  11. Q36Median review rating per product.
  12. Q37Median call_duration_seconds per agent.
  13. Q38P95 call_duration_seconds across all calls.
  14. Q39Median refund_amount across all returns.
  15. Q40Median net_salary from pay_slips.
  16. Q41P90 net_salary per department.
  17. Q42Median line quantity from order_items.
  18. Q43Median unit price (order_items) per category.
  19. Q44Median daily ad spend per platform.
  20. Q45Median points_balance of loyalty members per tier.
  21. Q46Median net_total per customer tier (Bronze..Platinum).
  22. Q47Median ticket resolution hours (resolved_date - created_date).
  23. Q48P95 ticket resolution hours per priority.
  24. Q49Median order value per month for 2025.
  25. Q50Median AND P95 net_total per region in one query (ARRAY of fractions).

DISTINCT ON - latest record per group

  1. Q51Latest order per customer: DISTINCT ON (cust_id) ORDER BY cust_id, order_date DESC.
  2. Q52The most-recent order_status per customer.
  3. Q53Latest review per customer.
  4. Q54Latest review per product.
  5. Q55Most-recent ticket per customer.
  6. Q56Latest payment per order.
  7. Q57Latest shipment row per order.
  8. Q58Most-recent inventory snapshot per warehouse x product.
  9. Q59Latest salary_history row per employee.
  10. Q60Most-recent price a product sold at (order_items via order_date).
  11. Q61Latest page_view per customer.
  12. Q62First (earliest) order per customer (ORDER BY order_date ASC).
  13. Q63Highest-value order per customer (ORDER BY net_total DESC).
  14. Q64Cheapest order per customer.
  15. Q65Latest call per agent.
  16. Q66Most-recent redemption per member.
  17. Q67Latest order per store.
  18. Q68Latest order per region.
  19. Q69Newest customer per region (join addresses).
  20. Q70Latest ad spend row per platform.
  21. Q71Most-recent expense per store.
  22. Q72Latest work_order per production line.
  23. Q73Top-rated review per product (ORDER BY rating DESC).
  24. Q74Latest attendance row per employee.
  25. Q75Most-recent transfer per account.

COMBINED / MIXED

  1. Q76Per customer: median net_total and the date of their latest order.
  2. Q77Per region: median order value and the single latest order.
  3. Q78Per store: median net_total and the most-recent order_status.
  4. Q79Median delivery time in days per warehouse (orders -> stores -> warehouse).
  5. Q80Take each customer's latest order, then report the median net_total of those.
  6. Q81P95 net_total per region, sorted descending.
  7. Q82Per product: median rating and the latest review's text.
  8. Q83Per agent: median call duration and their latest call timestamp.
  9. Q84Per customer: median resolution time and their latest ticket.
  10. Q85Per tier: median net_total and count of customers.
  11. Q86Median vs average net_total per region (show the gap).
  12. Q87P90 net_total per payment_mode, only modes with > 100 orders.
  13. Q88Latest *Delivered* order per customer (filter status, then DISTINCT ON).
  14. Q89Median net_total computed over each customer's most-recent order only.
  15. Q90Per category: median unit price and the latest selling price.
  16. Q91Per tier: median points_balance and the latest join_date.
  17. Q92P95 delivery days per region, only regions with > 500 orders.
  18. Q93Median order value for Gold/Platinum customers, per region.
  19. Q94Latest order per customer whose net_total is above the global median.
  20. Q95Per store: median net_total and P95 net_total as two columns.
  21. Q96Per month: median net_total and the latest order of that month.
  22. Q97Customers whose latest order is Returned (DISTINCT ON + filter).
  23. Q98Median refund_amount per region (returns -> orders -> stores -> region).
  24. Q99Median order value per region pivoted by quarter (Topic 21 pivot + percentile).
  25. Q100Executive KPI strip: median and P95 order value per region in one tidy result.

Combined ideas, multi-step thinking

CONCEPTUAL

  1. Q1Why does PERCENTILE_CONT interpolate while PERCENTILE_DISC snaps to a real row?
  2. Q2Show the exact output difference of CONT vs DISC on an even-count set {10,20,30,40}.
  3. Q3Why is median exactly PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x)?
  4. Q4How do you compute several percentiles in one pass using an ARRAY argument?
  5. Q5How do you unnest the array result of PERCENTILE_CONT(ARRAY[0.5,0.9,0.95])?
  6. Q6Why can't PERCENTILE_CONT appear directly in a WHERE clause?
  7. Q7How does DISTINCT ON break ties - what decides which row wins?
  8. Q8Why add a deterministic tiebreaker (e.g. order_id) to DISTINCT ON's ORDER BY?
  9. Q9DISTINCT ON vs ROW_NUMBER() OVER(...) - when is each cleaner?
  10. Q10Why must the DISTINCT ON column(s) be the leftmost ORDER BY keys?
  11. Q11Compare the cost of DISTINCT ON vs a correlated subquery for latest-per-group.
  12. Q12Can PERCENTILE_CONT be used as a window function with OVER (and what changes)?
  13. Q13What does mode() WITHIN GROUP return when two values tie for most-frequent?
  14. Q14How do you combine FILTER (WHERE ...) (Topic 21) with a percentile aggregate?
  15. Q15Why does a per-group median need GROUP BY but DISTINCT ON does not?
  16. Q16How does NTILE(4) (Topic 16) give quartile buckets vs PERCENTILE_CONT cut points?
  17. Q17Define percent_rank() vs cume_dist() precisely.
  18. Q18Why can P50 from PERCENTILE_CONT differ from the NTILE(2) boundary value?
  19. Q19How do you compute the interquartile range (P75 - P25)?
  20. Q20For outlier fences via 1.5xIQR, which percentiles do you need?
  21. Q21Why does DISTINCT ON keep the entire row, not just the key column?
  22. Q22How would you emulate DISTINCT ON in standard SQL (ROW_NUMBER filter)?
  23. Q23How do you take the median of a *computed* expression (e.g. delivery days)?
  24. Q24Do ordered-set aggregates include or ignore NULLs in the ordering input?
  25. Q25When is a single median misleading (e.g. a bimodal distribution)?

PERCENTILES WITH JOINS & GROUPS

  1. Q26Median and P95 net_total per region (full join chain).
  2. Q27Median delivery days per warehouse (orders -> stores -> warehouse).
  3. Q28Median, P90 and P95 of net_total per store in one query (ARRAY).
  4. Q29P25, P50, P75 of net_total per region (quartiles via ARRAY).
  5. Q30Interquartile range (P75 - P25) of net_total per region.
  6. Q31Median unit price per category (order_items -> products -> brand -> category).
  7. Q32Median basket size (items per order) per store.
  8. Q33Median order value per customer tier, only tiers with > 1000 customers.
  9. Q34Median review rating per brand.
  10. Q35Median call duration per agent, only agents with > 50 calls.
  11. Q36P95 ticket resolution hours per priority.
  12. Q37Median net_salary per department, with headcount alongside.
  13. Q38Median gap (days) between consecutive orders per customer (LAG, Topic 18, then median).
  14. Q39Median monthly revenue per region (group by month, then median across months).
  15. Q40Median ad spend per platform per quarter.
  16. Q41P90 delivery days per region, excluding Cancelled/Failed orders.
  17. Q42Median points_balance per tier with member counts.
  18. Q43Median refund_amount per category (returns -> products -> category).
  19. Q44Median order value per weekday (day-of-week).
  20. Q45Median order value for new vs returning customers (first-order flag via window).
  21. Q46Median gross margin per category (item revenue - cost_price).
  22. Q47Median time-to-first-order in days per registration-year cohort.
  23. Q48Median net_total per store, ranked, top 10 (Topic 16 RANK).
  24. Q49Median vs mean net_total per region with the spread and % difference.
  25. Q50P95 net_total per payment_mode with order counts (FILTER mix).

DISTINCT ON across tables

  1. Q51Latest order (full row) per customer, with the store name.
  2. Q52Most-recent review per product, with the reviewer's name.
  3. Q53Latest ticket per customer, with priority and subject.
  4. Q54Most-recent payment per order, with the payment_mode name.
  5. Q55Latest inventory snapshot per warehouse x product, with quantity.
  6. Q56Latest salary per employee, with department.
  7. Q57Most-recent price each product sold at (latest order_items by order_date).
  8. Q58Latest order per customer, plus days since that order.
  9. Q59First and latest order per customer (two DISTINCT ON queries, joined).
  10. Q60Most-recent *Delivered* order per customer.
  11. Q61Latest call per agent, with duration.
  12. Q62Newest customer per city (addresses).
  13. Q63Latest redemption per member, with points.
  14. Q64Most-recent expense per store, with amount.
  15. Q65Latest page_view per customer, with page_url.
  16. Q66Highest-rated review per product (tie-break by latest review_date).
  17. Q67Latest order per store, with net_total and status.
  18. Q68Most-recent work_order per production line.
  19. Q69Latest attendance per employee, with status.
  20. Q70Latest order per region (one row per region).
  21. Q71Most-recent ad spend per platform, with amount.
  22. Q72Latest shipment per order, with status and date.
  23. Q73Each customer's single largest order (DISTINCT ON by net_total DESC).
  24. Q74Latest transfer per account, with amount.
  25. Q75Most-recent ticket per agent.

PERCENTILE + PIVOT / WINDOW INTEGRATION

  1. Q76Median net_total per region x quarter pivot (Topic 21).
  2. Q77P95 net_total per region x month matrix.
  3. Q78Median delivery days per region x quarter.
  4. Q79Each customer's latest order plus that order's percent_rank of net_total (Topic 16).
  5. Q80Flag orders above their region's P90 (join the regional P90 back to each order).
  6. Q81Median order value per region with a rolling per-quarter view (window framing).
  7. Q82Customers whose latest order exceeds their own median order value.
  8. Q83Per store: median net_total, then rank stores by it (Topic 16).
  9. Q84Region median vs each store's median (gap to the regional benchmark).
  10. Q85NTILE(10) deciles of net_total vs PERCENTILE_CONT cut points, side by side.
  11. Q86Per category: median price and the latest selling price (DISTINCT ON + percentile).
  12. Q87Per agent: median resolution time and their latest ticket status.
  13. Q88Executive KPI strip: count, median, P95 order value per region.
  14. Q89Median basket size per store pivoted by quarter.
  15. Q90Customers above the global P95 net_total whose latest order is Returned.
  16. Q91Per region: median order value and the % of orders above it.
  17. Q92IQR-based outlier orders per region (net_total beyond P75 + 1.5.IQR).
  18. Q93Median monthly revenue per region with the latest month flagged.
  19. Q94Per tier: median order value and the single most-recent order.
  20. Q95P90 / P95 / P99 net_total per region (ARRAY) as three columns.
  21. Q96Median delivery days per warehouse, plus the latest snapshot quantity.
  22. Q97Rank regions by median order value, showing P95 alongside.
  23. Q98Per payment_mode: median order value pivoted by region.
  24. Q99Median net_total per customer cohort (registration year) x order year.
  25. Q100Full exec dashboard: per region - median, P90, P95 order value + latest order date.

Interview grade, edge cases

CONCEPTUAL

  1. Q1Implement median three ways (PERCENTILE_CONT, NTILE, manual row-offset) - tradeoffs.
  2. Q2Why can't PERCENTILE_CONT use an index, and what does that imply at scale?
  3. Q3Approximate percentiles for huge tables - strategies (sampling, t-digest concept).
  4. Q4Median as a window function vs as a grouped aggregate - output differences.
  5. Q5DISTINCT ON vs ROW_NUMBER vs correlated subquery vs LATERAL - full comparison.
  6. Q6What plan would DISTINCT ON over 150k orders produce (sort vs incremental)? (Topic 19)
  7. Q7Which index makes latest-per-group fast: composite (group_key, ts DESC)? (Topic 20)
  8. Q8Why does a covering index enable an index-only scan for DISTINCT ON? (Topic 20)
  9. Q9Computing P95 latency from event timestamps - common pitfalls.
  10. Q10How are NULLs handled in the ordering column of PERCENTILE_CONT?
  11. Q11Weighted median - why SQL lacks it natively and how to approximate it.
  12. Q12Median-of-group-medians vs the true global median - why they differ.
  13. Q13Explain the interpolation math of PERCENTILE_CONT (the fractional-row formula).
  14. Q14Trimmed mean (drop top/bottom 5%) implemented with percentiles.
  15. Q15Outlier detection: IQR fences vs z-score vs percentile clipping.
  16. Q16Why P50 != AVG, and what the gap reveals about skew.
  17. Q17DISTINCT ON pitfalls when the ORDER BY tiebreaker is non-unique.
  18. Q18Making "latest" reproducible when timestamps collide - tiebreak strategy.
  19. Q19Streaming / online percentile estimation - the core idea.
  20. Q20When to precompute group medians into a summary table / MV (Topic 25 preview).
  21. Q21Cost of many group medians - sort vs hash, and work_mem effects (Topic 19).
  22. Q22Median over a sliding window - the frame problem (Days 17-18).
  23. Q23Why GROUPING SETS combined with PERCENTILE_CONT can get expensive.
  24. Q24When PERCENTILE_DISC is required for an SLA (must be a real observed value).
  25. Q25Designing a percentile-based alerting metric (what to compute, how often).

DISTRIBUTION & PERCENTILE REPORTS

  1. Q26Five-number summary (min, P25, P50, P75, max) of net_total per region.
  2. Q27IQR and outlier fences per region; count the outliers beyond each fence.
  3. Q28Trimmed mean of net_total per region (drop below P5 and above P95).
  4. Q29P50/P90/P95/P99 of net_total per region (ARRAY) as columns, ranked by P95.
  5. Q30Delivery-time distribution per warehouse: median, P90, P95, max.
  6. Q31Median and P95 ticket resolution hours per priority; flag SLA breaches.
  7. Q32Per agent: median and P95 call duration; rank worst P95 (Topic 16).
  8. Q33Income distribution: P25/P50/P75 net_salary per department.
  9. Q34Per category: median margin % and P10 (worst-margin tail).
  10. Q35Basket-size distribution per store (median, P90).
  11. Q36Median gap-between-orders per customer, then the distribution of those medians.
  12. Q37Order-value percentiles per tier with skew (mean - median).
  13. Q38Revenue concentration per region: the P95 / P50 ratio.
  14. Q39Median order value per region x quarter with QoQ change (LAG, Topic 18).
  15. Q40Per store: median net_total and its percent_rank among all stores (Topic 16).
  16. Q41Delivery SLA: % of orders delivered within the regional P90 target, per region.
  17. Q42Median time-to-resolution by ticket priority.
  18. Q43P95 session/dwell metric per device type (web_events.page_views).
  19. Q44Per platform: median and P95 daily ad spend.
  20. Q45Median refund per category alongside the refund rate.
  21. Q46Cohort median order value (registration year) x subsequent order year matrix.
  22. Q47Per region: median order value among repeat customers only.
  23. Q48Weekly median revenue per region with a 4-week moving median (window).
  24. Q49Bucket each customer by lifetime value with NTILE(4); show the PERCENTILE cut points.
  25. Q50Outlier orders beyond P99 net_total per region, with customer and store context.

LATEST-PER-GROUP AT SCALE

  1. Q51Latest order per customer via DISTINCT ON; rewrite with ROW_NUMBER; confirm identical rows.
  2. Q52Latest Delivered order per customer with days-since and store.
  3. Q53Most-recent price per product (latest order_items) plus current cost margin.
  4. Q54Latest snapshot per warehouse x product + reorder flag (qty < reorder_level).
  5. Q55Latest salary per employee + % change vs the previous salary (LAG, Topic 18).
  6. Q56Each customer's latest order AND latest ticket in one report (two DISTINCT ON joined).
  7. Q57Latest review per product with a running count of reviews (window).
  8. Q58Most-recent status per shipment with age in days.
  9. Q59Latest order per store, ranked by recency gap (stalest stores first).
  10. Q60Customers whose latest order is Returned/Cancelled - churn-risk list.
  11. Q61Latest order per customer, then rank customers by recency across the base.
  12. Q62Most-recent payment per order, flagging where the latest payment Failed.
  13. Q63Latest attendance per employee, flagging absentees.
  14. Q64Per region: the single most-recent order with full context.
  15. Q65Latest redemption per member + points remaining.
  16. Q66First vs latest order per customer (two DISTINCT ON) + lifetime span in days.
  17. Q67Latest call per agent + that call's percentile duration (Topic 16).
  18. Q68Newest customer per city with tier.
  19. Q69Most-recent expense per store + YoY change.
  20. Q70Latest work_order per line + cycle time.
  21. Q71Each customer's single highest-value order (DISTINCT ON by value) + its percentile.
  22. Q72Latest order per payment_mode.
  23. Q73Most-recent inventory snapshot per warehouse with total stock value (qty x cost_price).
  24. Q74Latest ticket per agent with resolution status.
  25. Q75Build a "current state" set: exactly one latest order row per customer.

PRODUCTION KPI / EXEC DASHBOARDS

  1. Q76Executive KPI strip per region: orders, median AOV, P95 AOV, latest order date.
  2. Q77Region scorecard: median AOV, P95 AOV, median delivery days, SLA% - one row per region.
  3. Q78Store leaderboard: median net_total, P95, rank, decile (Topic 16).
  4. Q79Tier dashboard: per tier median AOV, P90, member count, latest signup.
  5. Q80Delivery SLA dashboard per warehouse: median, P95, breach count and %.
  6. Q81Support SLA: per priority median & P95 resolution, breach %, latest open ticket.
  7. Q82Agent performance: median & P95 call duration, latest call, rank.
  8. Q83Category margin board: median margin %, P10 tail, latest selling price.
  9. Q84Cohort retention value: median AOV by registration year x order year (pivot).
  10. Q85Outlier monitor: per region orders beyond P99, with customer and recency.
  11. Q86Churn-risk board: customers whose latest order is Returned + lifetime median.
  12. Q87Revenue health: per region median monthly revenue + QoQ trend (window).
  13. Q88Pricing drift: per product latest selling price vs median historical price.
  14. Q89Inventory freshness: per warehouse latest snapshot age + stockout flags.
  15. Q90Delivery funnel: median time order->ship and ship->deliver per region.
  16. Q91KPI matrix: region x quarter median AOV pivot with row and column medians.
  17. Q92Top vs bottom decile customers by lifetime value, with the cut points.
  18. Q93P95 latency board from web_events per device + peak hour (Topic 18 hour bucket).
  19. Q94Salary equity: per department median, P25, P75, IQR, headcount.
  20. Q95"Most-recent everything": per customer latest order / review / ticket in one wide row.
  21. Q96Region benchmark gap: each store's median vs its region's median.
  22. Q97Trimmed-mean revenue per region vs raw mean vs median (three columns).
  23. Q98Quarterly exec strip: median + P95 AOV per region per quarter, latest quarter flagged.
  24. Q99SLA alert query: regions whose P95 delivery exceeds target this month.
  25. Q100One-screen CXO dashboard: per region median/P90/P95 AOV, SLA%, latest order, rank.

Production scenarios, optimisation

CONCEPTUAL

  1. Q1Design an approximate-percentile service for billions of rows (t-digest / sketch) - concept.
  2. Q2Exact vs approximate percentiles: error budgets and when each is acceptable.
  3. Q3Incremental / streaming median maintenance as new orders arrive.
  4. Q4Reservoir sampling to bound the cost of a percentile query.
  5. Q5Merging percentile sketches across shards / partitions - why naive averaging fails.
  6. Q6Histogram-bucket percentiles vs PERCENTILE_CONT - accuracy / performance tradeoff.
  7. Q7Latest-per-group at 100M rows: index design, partitioning, MV refresh (Days 20/25).
  8. Q8Keeping a "current state" table fresh: trigger vs incremental MV vs batch reload.
  9. Q9DISTINCT ON vs LATERAL Top-1 vs window - plan and cost at scale (Topic 19).
  10. Q10Weighted percentiles (weight by order value) - algorithm sketch in SQL.
  11. Q11P99 / P99.9 stability - minimum sample size required per group.
  12. Q12Median in a sliding time window for a real-time dashboard - design.
  13. Q13Guarding percentile metrics against data skew and NULL floods.
  14. Q14Choosing PERCENTILE_DISC for a contractual SLA threshold - why exact-observed matters.
  15. Q15Backfilling historical percentiles into a summary fact table - idempotency.
  16. Q16Multi-tenant percentile isolation (per-tenant medians with no cross-leak).
  17. Q17Cost model: sort-based ordered-set aggregate vs hash; tuning work_mem (Topic 19).
  18. Q18Alerting on percentile drift (week-over-week P95 shift) - metric + threshold design.
  19. Q19Trimmed / winsorized aggregates as outlier-robust KPIs - when to prefer each.
  20. Q20Reconciling median-of-group-medians with the true global median for rollups.
  21. Q21Designing a percentile API contract (P50/P90/P95/P99) for the BI layer.
  22. Q22Idempotent "latest snapshot" pipeline tolerant of late-arriving data.
  23. Q23SCD type-2 read: latest-attribute-per-entity (e.g. tier over time) via DISTINCT ON.
  24. Q24Percentile-based guardrails for dynamic pricing.
  25. Q25When to push percentile / latest computation into an MV vs compute live (Topic 25).

MULTI-METRIC DISTRIBUTION ENGINES

  1. Q26One-query distribution engine: per region count, mean, P25/50/75/90/95/99, IQR, skew.
  2. Q27Robust region scorecard: trimmed mean, median, spread, outlier count.
  3. Q28Delivery-performance matrix per warehouse: median/P90/P95/max + SLA breach % + trend (window).
  4. Q29Customer-LTV distribution per tier: full P-spectrum + concentration (P95/P50 ratio).
  5. Q30Margin distribution per category: median, P10 tail, % of products below target margin.
  6. Q31Support SLA engine: per priority median/P95/P99 resolution, breach %, oldest-open (DISTINCT ON).
  7. Q32Agent ops board: median/P95 call duration, latest call, recency rank, peak hour (Topic 18).
  8. Q33Pricing corridor per product: P25-P75 selling-price band + latest-price drift.
  9. Q34Revenue concentration: per region top-decile share vs median (NTILE + percentile).
  10. Q35Basket analytics: per store median basket size, P90, mix vs region benchmark.
  11. Q36Cohort value spectrum: registration-year x order-year median AOV matrix (pivot) + diagonal trend.
  12. Q37Outlier engine: per region IQR fences + P99, with flagged orders and full context.
  13. Q38Seasonality view: per region median AOV per quarter with QoQ and YoY change (window).
  14. Q39Equity audit: per department salary P25/50/75, IQR, gap-to-org-median.
  15. Q40Web latency SLO: per device P50/P95/P99 session metric + peak-hour bucket.
  16. Q41Inventory health: per warehouse latest snapshot + stockout %, median days-of-cover.
  17. Q42Refund risk: per category median refund, refund rate, P95 refund, latest spike.
  18. Q43Delivery funnel timings: median + P95 of order->ship and ship->deliver per region.
  19. Q44Repeat-purchase cadence: per customer median inter-order gap; distribution of cadences.
  20. Q45Dynamic SLA targets: set each region's target = its own prior-quarter P90; measure attainment.
  21. Q46Price-band elasticity proxy: median units at each price-percentile band per category.
  22. Q47Multi-stage pipeline: per-customer median -> per-region median-of-medians vs true global.
  23. Q48Anomaly sweep: regions whose P95 AOV shifted more than X% week-over-week.
  24. Q49Winsorized revenue: clamp net_total to [P1,P99] per region; compare to raw totals.
  25. Q50Full distribution pivot: region x quarter median AOV with row/col medians + grand median.

LATEST-STATE / SCD PIPELINES

  1. Q51Build a "customer current state" row: latest order, review, ticket, payment in one wide row.
  2. Q52SCD-type-2 read: latest tier per customer over time (DISTINCT ON tier_updated_at).
  3. Q53Idempotent latest-snapshot: per warehouse x product newest row + reorder flag + value.
  4. Q54Pricing book: most-recent selling price per product + corridor + days-since-change.
  5. Q55Churn engine: customers whose latest order is Returned/Cancelled + lifetime median + recency decile.
  6. Q56Employee current comp: latest salary per employee + % change + percentile within department.
  7. Q57Latest-vs-first delta per customer: span days, value growth, order-frequency change.
  8. Q58Per-region freshest order with full enrichment (store, customer, items, payment).
  9. Q59"Most-recent activity" unifier across orders/reviews/tickets/calls per customer (latest of any).
  10. Q60Stalest entities: stores / agents ranked by recency of their last activity.
  11. Q61Latest payment status per order -> reconcile a Failed-latest against finance.payments.
  12. Q62Current inventory valuation: latest snapshot per SKU x cost_price -> total value.
  13. Q63Latest attendance per employee -> absentee list + tenure context.
  14. Q64Versioned price changes: per product the sequence of distinct prices, latest highlighted (window + DISTINCT ON).
  15. Q65Per customer: latest order's percentile rank vs their own order history (Topic 16).
  16. Q66Build a "delta since last snapshot" per warehouse x product.
  17. Q67Latest campaign spend per platform + pacing vs budget.
  18. Q68Most-recent ticket per agent + open-aging + SLA flag.
  19. Q69Customer "last seen": unified max timestamp across web_events and orders.
  20. Q70Latest redemption + remaining points + tier-benefit eligibility.
  21. Q71SCD read: latest address per customer (if multiple) for the current city.
  22. Q72Per store: latest order + rolling 30-day median context (window + DISTINCT ON).
  23. Q73Freshness SLA: entities whose latest activity is older than a threshold.
  24. Q74Latest work_order per line + cycle-time percentile.
  25. Q75One-pass "current state of the business": latest KPI snapshot per region.

PRODUCTION ANALYTICS SYSTEMS

  1. Q76CXO single-screen: per region median/P90/P95 AOV, SLA%, latest order, churn-risk count, rank.
  2. Q77Real-time-ish ops monitor: per warehouse P95 delivery, breach %, freshest snapshot age.
  3. Q78Pricing governance: per product latest price vs P25-P75 corridor; flag drift; latest change date.
  4. Q79Customer health score: blend latest-recency decile + lifetime-median percentile + return flag.
  5. Q80SLA alerting pipeline: regions/priorities breaching P95 targets this period vs last (window).
  6. Q81Cohort LTV dashboard: reg-year x order-year median AOV pivot + retention curve + latest cohort.
  7. Q82Outlier & fraud sweep: per region P99 fences + customers with an anomalous latest order.
  8. Q83Inventory replenishment board: latest snapshot per SKU, median days-of-cover, stockout risk.
  9. Q84Workforce equity report: per department salary percentile spread + gap-to-median + latest hire.
  10. Q85Support command center: per priority median/P95/P99 resolution, breach %, oldest-open ticket.
  11. Q86Revenue distribution monitor: per region winsorized vs raw vs median, week-over-week drift.
  12. Q87Web performance SLO board: per device P50/P95/P99 + peak hour + worst recent session.
  13. Q88Margin protection: per category median margin, P10 tail, products below floor, latest price.
  14. Q89Delivery-funnel SLA: per region median + P95 of each stage, end-to-end P95, breach %.
  15. Q90Pricing-elasticity matrix: price-band percentile x median units per category.
  16. Q91"Latest everything" mart: one wide current-state row per customer for the BI layer.
  17. Q92Dynamic-target SLA engine: target = prior-quarter P90 per region; this-quarter attainment.
  18. Q93Anomaly digest: top regions by week-over-week P95 AOV shift, with drill-down context.
  19. Q94Executive percentile API result: per region ARRAY[P50,P90,P95,P99] for AOV, delivery, resolution.
  20. Q95Concentration & equity: per region revenue P95/P50 ratio + top-decile share + median.
  21. Q96Full reshape pipeline: unpivot KPIs -> percentile per metric -> re-pivot region x metric (Topic 21).
  22. Q97Freshness + distribution combined: per region latest order + median/P95 in one statement.
  23. Q98Multi-grain rollup: store -> region -> all medians via GROUPING SETS + reconciliation note.
  24. Q99Production "morning report": per region orders, median/P95 AOV, SLA%, churn-risk, freshest order, rank - one query.
  25. Q100Capstone: the per-region executive distribution dashboard (median, P90, P95, P99, IQR, SLA%, latest order, decile rank) as one production query.