TheShiraverseSELECT * TheShiraverseCurriculumPracticeKahaniFAQGet StartedPlaygroundNukteTheShiraverse ↗
Practice › Topic 17

Window Functions Part 2: 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. Q1How does SUM(x) OVER () differ from SUM(x) with GROUP BY?
  2. Q2What does SUM(x) OVER (PARTITION BY g) compute for each row?
  3. Q3What does adding ORDER BY inside OVER() do to SUM() (running total)?
  4. Q4What is the default window frame when ORDER BY is present?
  5. Q5What is the default frame when ORDER BY is absent?
  6. Q6Define a "running total" in one sentence.
  7. Q7How do you compute each row's % of the grand total with a window?
  8. Q8How do you compute each row's % of its partition total?
  9. Q9What does AVG(x) OVER (PARTITION BY g) give (group average on every row)?
  10. Q10Why keep detail rows AND a group aggregate in the same result with windows?
  11. Q11What does COUNT(*) OVER (PARTITION BY g) return?
  12. Q12Explain ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
  13. Q13Explain ROWS BETWEEN 6 PRECEDING AND CURRENT ROW (7-row window).
  14. Q14Difference between ROWS and RANGE frame modes (intro).
  15. Q15Why does a running total need a deterministic ORDER BY?
  16. Q16Can you mix a window aggregate and a plain aggregate? (no - why)
  17. Q17What is a moving (rolling) average conceptually?
  18. Q18Why does SUM() OVER() avoid a self-join for "value vs group total"?
  19. Q19What does MAX(x) OVER (PARTITION BY g) give?
  20. Q20How is "share of total" different from a rank?
  21. Q21Why must you be careful with frames and ties in the ORDER BY?
  22. Q22What is a cumulative count and how do you build it?
  23. Q23When would you PARTITION BY DATE_TRUNC('month', d)?
  24. Q24Can window aggregates appear in WHERE? What's the workaround?
  25. Q25Name one analyst use each for running total, moving avg, and % of total.

AGGREGATE OVER

  1. Q26Show each order with the grand total net_total alongside (SUM OVER ()).
  2. Q27Show each order with its customer's total spend (SUM OVER PARTITION BY cust_id).
  3. Q28Show each product with its brand's average price.
  4. Q29Show each employee with their department's average salary.
  5. Q30Show each order with the count of orders for its store.
  6. Q31Show each product with the max price in its brand.
  7. Q32Show each review with the average rating for its product.
  8. Q33Show each order with its store's total revenue.
  9. Q34Show each employee with the min and max salary of their store.
  10. Q35Show each order_item with the order's total net_amount.
  11. Q36Show each customer's order with the customer's order count.
  12. Q37Show each product with its category's average price (via brand).
  13. Q38Show each ticket with the count of tickets for its agent.
  14. Q39Show each call with the average duration for its agent.
  15. Q40Show each pay_slip with the company-wide average net_salary.
  16. Q41Show each shipment with the average delivery days for its courier.
  17. Q42Show each ad spend with the platform's total spend.
  18. Q43Show each store with its region's total square_ft.
  19. Q44Show each order with the customer's average order value.
  20. Q45Show each product with the supplier's product count.
  21. Q46Show each member with the average points_balance of their tier.
  22. Q47Show each order with both the store total and the grand total.
  23. Q48Show each review with the customer's review count.
  24. Q49Show each employee with the company headcount (COUNT(*) OVER ()).
  25. Q50Show each product with brand average and overall average side by side.

RUNNING TOTALS & % OF TOTAL

  1. Q51Running total of net_total over orders ordered by order_date.
  2. Q52Running total of net_total per customer ordered by order_date.
  3. Q53Running count of orders per store over time.
  4. Q54Each order's % of the grand total revenue.
  5. Q55Each order's % of its customer's total spend.
  6. Q56Each product's % of its brand's total price (catalog share).
  7. Q57Cumulative revenue by month (aggregate to month, then running sum).
  8. Q58Running total of expenses per department (finance.expenses or stores.expenses).
  9. Q59Each employee's salary as % of department payroll.
  10. Q60Running total of points_balance per tier ordered by join_date.
  11. Q61Each store's revenue as % of its region total.
  12. Q62Cumulative order count per customer (purchase number).
  13. Q63Each review's contribution to product's rating sum (running).
  14. Q64Running total of ad spend per campaign over spend_date.
  15. Q65Each order_item's % of the order total.
  16. Q66Cumulative revenue by week across the whole company.
  17. Q67Each category's revenue as % of grand total (aggregate + window).
  18. Q68Running total of net_salary per employee across months.
  19. Q69Each product's units as % of brand units sold.
  20. Q70Cumulative new customers by registration month.
  21. Q71Running total of refunds per customer over return_date.
  22. Q72Each region's store count as % of all stores.
  23. Q73Cumulative revenue per store ordered by date; show first/last.
  24. Q74Each call's duration as % of the agent's total talk time.
  25. Q75Pareto check: cumulative % of revenue by customer (sorted desc).

FRAME BASICS (ROWS BETWEEN)

  1. Q763-row moving average of daily revenue (1 preceding, current, 1 following).
  2. Q777-day moving average of daily order count (6 preceding to current).
  3. Q78Trailing 3-order average net_total per customer.
  4. Q79Running max of net_total per customer (UNBOUNDED PRECEDING to CURRENT).
  5. Q80Running min price seen so far per brand (ordered by product_id).
  6. Q81Moving sum of last 5 orders' value per customer.
  7. Q82Centered 3-point average of monthly revenue.
  8. Q83Trailing 7-day sum of revenue (daily series).
  9. Q84Difference of current revenue from its 3-row moving average.
  10. Q85Running average rating per product as reviews accumulate.
  11. Q86Last-3 average call duration per agent.
  12. Q87Cumulative max points_balance per customer over join_date.
  13. Q884-week moving average of new signups.
  14. Q89Trailing 30-row sum of expenses per department.
  15. Q90Moving average of order value with frame ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING.
  16. Q91Running total that uses an explicit UNBOUNDED PRECEDING frame.
  17. Q92Compare default-frame running total vs explicit ROWS frame (same result?).
  18. Q933-month moving average of revenue per store.
  19. Q94Trailing average delivery time over last 10 shipments per courier.
  20. Q95Smoothed daily web page-view count (7-day moving average).
  21. Q96Running sum that resets per customer (PARTITION BY) with a frame.
  22. Q97Moving average of ratings over last 5 reviews per product.
  23. Q98Trailing 3-period revenue growth setup (sum windows; growth itself is Topic 18).
  24. Q99Cumulative units sold per product over order_date.
  25. Q100Build a daily revenue table with 7-day moving avg AND cumulative total in one query.

Combined ideas, multi-step thinking

CONCEPTUAL

  1. Q1Why does PARTITION BY reset a running total at each partition boundary?
  2. Q2Default frame (RANGE UNBOUNDED PRECEDING) vs ROWS UNBOUNDED PRECEDING - when do they differ?
  3. Q3Why can RANGE with ties sum more rows than you expect?
  4. Q4How to build a 7-day moving average correctly when some days are missing?
  5. Q5Why is gap-filling (date series) sometimes required before a moving average?
  6. Q6ROWS BETWEEN 6 PRECEDING AND CURRENT ROW - how many rows in the frame?
  7. Q7How to reset a running total at the year boundary (PARTITION BY year)?
  8. Q8Why compute % of partition total with SUM() OVER (PARTITION BY g) in the denominator?
  9. Q9Difference between a trailing and a centered moving average.
  10. Q10Why must the ORDER BY be deterministic for a reproducible running total?
  11. Q11How does a frame interact with PARTITION BY (frame is within partition)?
  12. Q12When is RANGE BETWEEN INTERVAL '7 days' PRECEDING ... valid (date/numeric ORDER BY)?
  13. Q13Why might a moving average over ROWS differ from over RANGE on daily data?
  14. Q14How to compute cumulative distinct counts (and why it's hard with windows)?
  15. Q15Explain "percent of running total" vs "running percent".
  16. Q16Why pre-aggregate to a grain (day/month) before windowing a time series?
  17. Q17How to show both the partition total and the running total in one row.
  18. Q18What does FIRST_VALUE/LAST_VALUE need (a frame) to be correct? (preview)
  19. Q19Why is LAST_VALUE often "wrong" without an explicit full frame?
  20. Q20How to compute a moving sum that ignores the current row (exclude current).
  21. Q21Why does a running max never decrease and a running min never increase?
  22. Q22How to combine a window aggregate with a GROUP BY in layered steps.
  23. Q23When to use RANGE vs ROWS for financial running balances.
  24. Q24How to compute share-of-total within multiple partition levels at once.
  25. Q25Why are window aggregates ideal for "detail + subtotal" report rows?

PARTITIONED RUNNING TOTALS

  1. Q26Running total of net_total per customer over order_date.
  2. Q27Running order count per store over order_date.
  3. Q28Cumulative revenue by month per store (aggregate then window).
  4. Q29Running total of expenses per department over expense_date.
  5. Q30Cumulative units sold per product over order_date.
  6. Q31Running total of points_balance per tier over join_date.
  7. Q32Cumulative new customers per registration month per region.
  8. Q33Running revenue per region by month.
  9. Q34Running total of refunds per customer over return_date.
  10. Q35Cumulative ad spend per platform over spend_date.
  11. Q36Running count of reviews per product over review_date.
  12. Q37Running net_salary total per employee over salary month.
  13. Q38Cumulative orders per customer with the purchase number (1,2,3...).
  14. Q39Running revenue per payment_mode by month.
  15. Q40Cumulative shipments per courier by week.
  16. Q41Running total of order value per store, reset each year.
  17. Q42Cumulative call minutes per agent by day.
  18. Q43Running revenue per category by month (via brand).
  19. Q44Cumulative distinct products ordered per customer (approx via min order_date per product).
  20. Q45Running headcount per department by joining_date.
  21. Q46Cumulative revenue per city by month (via addresses).
  22. Q47Running total of net_amount per order over order_item_id.
  23. Q48Cumulative tickets per agent by created_date.
  24. Q49Running revenue per brand by week.
  25. Q50Running total per customer with both partition total and running total shown.

MOVING AVERAGES & FRAMES

  1. Q517-day moving average of daily revenue.
  2. Q527-day moving average of daily order count.
  3. Q533-month moving average of monthly revenue per store.
  4. Q54Trailing 5-order average net_total per customer.
  5. Q55Centered 3-point moving average of monthly signups.
  6. Q564-week moving average of weekly revenue per region.
  7. Q57Trailing 30-day sum of revenue (daily series).
  8. Q58Moving average rating over last 5 reviews per product.
  9. Q597-day moving average of web page-views (gap-filled by date series).
  10. Q60Trailing 3-month average expenses per department.
  11. Q61Moving average delivery time over last 10 shipments per courier.
  12. Q62Running average order value per customer (expanding window).
  13. Q63Difference of daily revenue from its 7-day moving average (anomaly setup).
  14. Q645-period moving sum of units sold per product.
  15. Q65Trailing 6-month average net_salary per employee.
  16. Q66Moving average of call duration over last 20 calls per agent.
  17. Q673-row moving average with ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING.
  18. Q68Rolling 90-day revenue total (RANGE INTERVAL on a date series).
  19. Q69Trailing average review rating per customer (last 3).
  20. Q707-day moving average AND daily value side by side for spotting spikes.
  21. Q71Moving average that excludes the current row (n PRECEDING to 1 PRECEDING).
  22. Q724-quarter moving average of revenue (quarterly aggregate).
  23. Q73Trailing 10-order average margin per product.
  24. Q74Rolling 7-day distinct-customer count approximation per day.
  25. Q75Compare ROWS vs RANGE moving average on a series with duplicate dates.

SHARE-OF-TOTAL & RESET-AT-BOUNDARY

  1. Q76Each order's % of its customer's total spend.
  2. Q77Each product's % of its brand's total units sold.
  3. Q78Each store's revenue as % of its region total.
  4. Q79Each category's revenue as % of grand total.
  5. Q80Each employee's salary as % of department payroll.
  6. Q81Running total of revenue reset at each year boundary (PARTITION BY year).
  7. Q82Running total reset at each month per store.
  8. Q83Each month's revenue as % of its year's revenue.
  9. Q84Pareto: cumulative % of revenue by customer (sorted desc) - find the top 20%.
  10. Q85Each agent's resolved tickets as % of their category total.
  11. Q86Each product's review count as % of brand review count.
  12. Q87Running revenue per store reset quarterly.
  13. Q88Each campaign's spend as % of its platform total.
  14. Q89Each region's contribution % to monthly company revenue.
  15. Q90Cumulative % of units by product within category (ABC analysis setup).
  16. Q91Each customer's monthly spend as % of their lifetime spend.
  17. Q92Reset running headcount at each department.
  18. Q93Each order line's % of order total (basket composition).
  19. Q94Each city's spend as % of region spend (via addresses).
  20. Q95Running revenue reset at year boundary AND % of that year's total.
  21. Q96Each warehouse's stock as % of its region's stock.
  22. Q97Each tier's points as % of all points.
  23. Q98Each weekday's revenue as % of the week (PARTITION BY ISO week).
  24. Q99Top-20% revenue customers via cumulative share (Pareto cutoff).
  25. Q100Build a monthly report: revenue, running YTD total, and % of year - per store.

Interview grade, edge cases

CONCEPTUAL

  1. Q1Why is LAST_VALUE wrong by default, and what frame fixes it?
  2. Q2ROWS vs RANGE vs GROUPS frame modes - precise differences.
  3. Q3Why does a 7-day moving average need a complete date series first?
  4. Q4RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW - requirements and gotchas.
  5. Q5How to compute a moving average that's correct at series edges (partial windows).
  6. Q6Why FIRST_VALUE + frame = the partition's anchor value on every row.
  7. Q7How to exclude the current row from a frame (and why for "peer average").
  8. Q8Cumulative distinct count - why windows can't do it directly; the workaround.
  9. Q9Reset-at-boundary running totals: PARTITION BY period vs frame tricks.
  10. Q10Why pre-aggregate to a grain before applying frames on a fact table.
  11. Q11Detect anomalies as deviation from a trailing moving average - design.
  12. Q12Share-of-total at two partition levels in one query (region and grand).
  13. Q13Why RANGE with duplicate ORDER BY keys sums all ties into one frame step.
  14. Q14NTH_VALUE use-cases and its frame dependency.
  15. Q15Rolling median - why it's hard with standard window functions.
  16. Q16Why "running total then % of final" needs the partition total, not the frame.
  17. Q17How to compute month-to-date and prior-month-to-date in one pass (no LAG).
  18. Q18Window aggregate + HAVING-like filter: where does the filter go?
  19. Q19Frame performance: why huge RANGE frames can be O(n^2) without care.
  20. Q20Gap-filling with generate_series + LEFT JOIN before windowing - pattern.
  21. Q21Why a centered moving average shifts trend timing vs trailing.
  22. Q22Compute "% to peak" using a running MAX with a frame.
  23. Q23Building a contribution-to-cumulative (Pareto) curve correctly.
  24. Q24Why FILTER (WHERE ...) inside a window aggregate is not allowed; alternative.
  25. Q25Combining multiple frames (trailing-7 and trailing-30) in one query.

ROLLING METRICS

  1. Q26SCENARIO: Finance wants a gap-filled daily revenue series with a 7-day moving average.
  2. Q2730-day rolling revenue total per store (RANGE on a date series).
  3. Q287-day rolling distinct-customer count (approximation) per day.
  4. Q29Trailing 3-month revenue and its growth base (no LAG - just the trailing sum).
  5. Q30Rolling 28-day order count with a 4-week moving average.
  6. Q31Moving average of delivery time over last 20 shipments per courier.
  7. Q32Deviation of daily revenue from its trailing 7-day average (flag > 2x).
  8. Q33Rolling 90-day revenue per region, gap-filled.
  9. Q34Trailing-10 average margin per product; flag drops.
  10. Q357-day moving average of web page-views per device_type.
  11. Q36Rolling 6-month average net_salary per department.
  12. Q37Cumulative revenue with a 30-day trailing sum side by side.
  13. Q38Rolling 14-day ticket volume per category with moving average.
  14. Q39Trailing 5-order average basket size per customer.
  15. Q40Rolling weekly active customers (distinct per trailing 7 days).
  16. Q41Moving average of ratings over last 10 reviews per product.
  17. Q42Rolling 30-day refund total per customer.
  18. Q433-month moving average revenue per category, gap-filled.
  19. Q44Trailing 7-day cumulative units sold per product.
  20. Q45Rolling 4-week signups with moving average per region.
  21. Q46Trailing-20 average call duration per agent; flag fatigue (rising trend).
  22. Q47Rolling 90-day GMV with a 7-day smoothed line.
  23. Q48Moving average of order value excluding the current order (peer baseline).
  24. Q49Rolling 12-month revenue (TTM) per store.
  25. Q50Daily revenue, 7-day MA, 30-day MA in one query (multiple frames).

FIRST_VALUE / LAST_VALUE / NTH_VALUE

  1. Q51First order value per customer on every row (FIRST_VALUE).
  2. Q52Last (most recent) order value per customer with a correct full frame.
  3. Q53Each order's value vs the customer's first order value (ratio).
  4. Q54First and last review rating per product on every row.
  5. Q55NTH_VALUE: the 2nd order value per customer on every row.
  6. Q56Each product's price vs its brand's cheapest (FIRST_VALUE by price asc).
  7. Q57Each employee's salary vs their department's top salary (FIRST_VALUE desc).
  8. Q58First and current cumulative revenue per store (anchor vs running).
  9. Q59Each month's revenue vs the year's first month (indexing to 100).
  10. Q60Last delivered date per courier on every shipment row.
  11. Q61Each call's duration vs the agent's longest call (FIRST_VALUE desc).
  12. Q62First purchase date per customer attached to every order.
  13. Q63Each order's value vs the store's max order value (peak).
  14. Q64% to peak: running value / running MAX per series.
  15. Q65Each product's price vs its supplier's most expensive product.
  16. Q66First and last snapshot quantity per (warehouse, product).
  17. Q67Each campaign's spend vs the platform's biggest campaign.
  18. Q68NTH_VALUE: 3rd-highest order value per customer on every row (with frame).
  19. Q69Each region's monthly revenue vs its best month (FIRST_VALUE by revenue desc).
  20. Q70Each customer's latest tier vs first tier (anchor comparison).
  21. Q71Each pay_slip vs the employee's first recorded net_salary.
  22. Q72Each product's units vs brand's best-seller units (FIRST_VALUE).
  23. Q73First and last order value per customer in one row (both ends).
  24. Q74Each store's revenue vs region's top store revenue.
  25. Q75Anchor every row to the partition's first AND last values, compute the span.

SHARE / RESET / REPORTS

  1. Q76SCENARIO: CFO wants a monthly P&L strip: revenue, YTD revenue, and % of year - per region.
  2. Q77Pareto curve: cumulative % of revenue by customer; identify the top-20% set.
  3. Q78ABC product classification via cumulative % of units within category.
  4. Q79Each order's % of customer spend AND % of store revenue (two windows).
  5. Q80Running revenue reset per year with % of that year's total per month.
  6. Q81Contribution analysis: each category's monthly % of company revenue.
  7. Q82Month-to-date vs full-month revenue per store (frame to month end).
  8. Q83Each employee's salary percentile-ish share within department payroll.
  9. Q84Rolling 7-day revenue with its % of trailing 30-day revenue.
  10. Q85Weekly revenue as % of its month (reset monthly).
  11. Q86Each product's running share of brand revenue over time.
  12. Q87Customer cohort: cumulative spend per customer indexed to first month = 100.
  13. Q88Each region's quarter revenue as % of its year.
  14. Q89Detect anomaly months: revenue > 1.5x trailing 3-month average.
  15. Q90Each store's daily revenue as % of its trailing 7-day total.
  16. Q91Cumulative refunds as % of cumulative revenue per customer.
  17. Q92Each agent's daily resolved tickets as % of their trailing-week total.
  18. Q93Reset running GMV quarterly and show quarter-to-date %.
  19. Q94Top-20% products by cumulative units (Pareto) per category.
  20. Q95Each campaign's spend as running % of platform spend over time.
  21. Q96Monthly revenue with YTD total and YTD % growth-base (growth itself is Topic 18).
  22. Q97Each customer's order as % of their trailing-90-day spend.
  23. Q98Region revenue contribution waterfall (cumulative share, sorted).
  24. Q99Each warehouse's stock as % of region stock AND % of company stock.
  25. Q100Executive dashboard query: per region per month - revenue, YTD, %-of-year, 3-mo MA.

Production scenarios, optimisation

CONCEPTUAL

  1. Q1Architect a daily KPI query with 7-day, 30-day, and YTD windows in one pass.
  2. Q2RANGE vs ROWS vs GROUPS - precise frame semantics and when each is correct.
  3. Q3Why gap-filling must precede rolling windows; build the date spine.
  4. Q4LAST_VALUE/NTH_VALUE frame correctness; the UNBOUNDED FOLLOWING fix.
  5. Q5Rolling distinct counts: why windows fail and how to approximate/solve.
  6. Q6Rolling median: approaches (percentile_cont per window via LATERAL) and cost.
  7. Q7Performance of large RANGE INTERVAL frames; mitigation strategies.
  8. Q8Multi-level share-of-total in one query (line/order/customer/grand).
  9. Q9Reproducible reset-at-boundary cumulative metrics across refreshes.
  10. Q10Anomaly detection: deviation-from-trailing-MA, z-score within a window.
  11. Q11Why FILTER isn't allowed in window aggregates; CASE-inside-aggregate alternative.
  12. Q12Building Pareto/ABC curves correctly with cumulative share + cut-points.
  13. Q13Combining window aggregates with GROUP BY in layered CTEs (grain discipline).
  14. Q14Centered vs trailing MA: trend-timing tradeoffs for forecasting inputs.
  15. Q15Exclude-current-row frames for unbiased peer baselines.
  16. Q16Index-to-100 time series (each row / partition's first value) at scale.
  17. Q17% to running peak (drawdown) using running MAX frames.
  18. Q18TTM (trailing-twelve-month) metrics with monthly grain.
  19. Q19Why duplicate ORDER BY keys + RANGE inflate frames; ROWS as the fix.
  20. Q20Designing a query that emits detail + subtotal + grand total rows.
  21. Q21Rolling cohort retention inputs purely from aggregation windows (no LAG).
  22. Q22Multi-frame correctness when partitions have sparse/uneven dates.
  23. Q23Materializing the date spine + facts before windowing for speed.
  24. Q24Contribution waterfall ordering and cumulative share semantics.
  25. Q25When to push windowing to an MV (Topic 25) vs compute on the fly.

MULTI-FRAME ROLLING KPIs

  1. Q26SCENARIO: Build the daily revenue KPI line: value, 7-day MA, 30-day MA, YTD - gap-filled.
  2. Q27Per store: daily revenue with 7/28/90-day moving sums.
  3. Q28Rolling 7-day and 30-day active-customer counts per day.
  4. Q29Trailing-3-month and trailing-12-month revenue per region.
  5. Q30Daily orders with 7-day MA and deviation flag (> 2x MA).
  6. Q31Rolling 30-day GMV and its % of trailing 90-day GMV.
  7. Q32Per product: trailing-7 and trailing-30 units with both moving averages.
  8. Q33Rolling weekly signups, 4-week MA, and 12-week MA per region.
  9. Q34Delivery time: trailing-20 and trailing-100 moving average per courier.
  10. Q35Per agent: trailing-week and trailing-month resolved-ticket counts.
  11. Q36Daily refunds with 7-day MA and refund-spike flag.
  12. Q37Rolling 90-day revenue per category, gap-filled, with 7-day smoothing.
  13. Q38Per warehouse: trailing-30-day stock movement (snapshots) moving average.
  14. Q39Web page-views: 7-day MA per device and the device's share of daily total.
  15. Q40Rolling 6-month and 12-month net_salary average per department.
  16. Q41Per customer: trailing-90-day spend and trailing-365-day spend.
  17. Q42Daily revenue z-score within a trailing 30-day window (anomaly score).
  18. Q43Rolling 14-day ticket volume with MA per priority.
  19. Q44Per brand: trailing-30-day revenue and its rank-free % of trailing-90.
  20. Q45Rolling 7-day and 30-day average basket size per store.
  21. Q46Trailing-12-month revenue (TTM) with month-over-month base (sums only).
  22. Q47Per region: 4-week and 12-week moving average of new customers.
  23. Q48Rolling 30-day distinct products sold per store (approximation).
  24. Q49Daily revenue, 7-day MA, and % deviation from MA per store.
  25. Q50One query: per store per day - revenue, 7/30-day MA, YTD, and YTD %-of-year.

ANCHORED VALUES, PARETO & ABC

  1. Q51SCENARIO: Product team wants ABC classification: cumulative % of revenue per category -> A/B/C.
  2. Q52Pareto: top-20% of customers driving what % of revenue (cumulative share).
  3. Q53Index each store's monthly revenue to its first month = 100.
  4. Q54Each order vs the customer's first and last order value (FIRST/LAST_VALUE).
  5. Q55% to peak (drawdown) of cumulative revenue per store.
  6. Q56ABC classification of products by units within brand.
  7. Q57Each region's month vs its best month (FIRST_VALUE by revenue desc).
  8. Q58Cumulative revenue contribution curve per category (waterfall order).
  9. Q59NTH_VALUE: each customer's 2nd and 3rd order values on every row.
  10. Q60Each product's price vs brand cheapest and dearest (FIRST + LAST_VALUE).
  11. Q61Customer spend indexed to first active month per acquisition cohort.
  12. Q62Pareto cutoff: smallest set of products making 80% of revenue.
  13. Q63Each campaign vs platform's biggest campaign spend (anchor).
  14. Q64Drawdown from running max of cumulative GMV (max underwater %).
  15. Q65ABC by margin contribution per category.
  16. Q66Each store's revenue vs region's top and bottom store (span).
  17. Q67Top-20% SKUs by cumulative units within each warehouse.
  18. Q68Each employee's salary vs department first/last by hire order.
  19. Q69Cumulative % of refunds by customer (who drives returns).
  20. Q70Each month indexed to year's first month per region (=100).
  21. Q71Contribution of each payment_mode to cumulative revenue.
  22. Q72First/last snapshot per (warehouse, product) and net change.
  23. Q73Pareto of agents by resolved-ticket contribution.
  24. Q74ABC customers by lifetime spend (A=top 80% cum, etc.).
  25. Q75Each product's running share of brand revenue with peak-share month.

EXECUTIVE DASHBOARDS

  1. Q76SCENARIO: Build the exec monthly dashboard per region: revenue, YTD, %-of-year, 3-mo MA, contribution %.
  2. Q77Daily company KPI: revenue, orders, AOV, 7-day MA of each, gap-filled.
  3. Q78Store scorecard: revenue, region-share %, YTD, trailing-90 MA.
  4. Q79Product performance board: units, brand-share %, ABC class, trailing-30 MA.
  5. Q80Customer value board: lifetime spend, spend percentile (window), recency, trailing-90.
  6. Q81Category monthly board: revenue, % of company, cumulative YTD, 3-mo MA.
  7. Q82Courier SLA board: avg delivery, trailing-100 MA, % of shipments under 2 days.
  8. Q83Agent productivity board: resolved/day, trailing-week MA, share of category.
  9. Q84Region waterfall: monthly revenue contribution to company cumulative.
  10. Q85Marketing board: platform spend, share %, trailing-30 MA, cumulative.
  11. Q86Warehouse health board: stock, region-share %, trailing-30 movement MA.
  12. Q87Cohort revenue board: per signup-month cumulative spend indexed to 100.
  13. Q88Daily anomaly board: revenue, 7-day MA, z-score, flagged spikes.
  14. Q89Pareto board: cumulative customer revenue share with the 80% line.
  15. Q90P&L strip: revenue, expenses (windowed), running margin per month per region.
  16. Q91Retention input board: rolling weekly active customers + 4-week MA.
  17. Q92Brand board: revenue, category-share %, YTD, ABC class.
  18. Q93Store-of-month: highest trailing-30 revenue store per region (aggregate window).
  19. Q94Inventory turns board: trailing-90 COGS / avg stock per product.
  20. Q95Channel board: web sessions, conversion proxy, 7-day MA per device.
  21. Q96Executive "one big query": region x month with 8 windowed KPIs.
  22. Q97Top-line board: company revenue, YTD, %-of-year, TTM in one query.
  23. Q98Quartile-free contribution board (pure cumulative share, no NTILE).
  24. Q99Returns board: refund total, % of revenue, trailing-30 MA per category.
  25. Q100Full monthly exec pack: revenue, YTD, %-of-year, 3-mo MA, contribution %, drawdown - per region.