TheShiraverseSELECT * TheShiraverseCurriculumPracticeKahaniFAQGet StartedPlaygroundNukteTheShiraverse ↗
Practice › Topic 18

Window Functions Part 3: 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 LAG(x) return for a given row?
  2. Q2What does LEAD(x) return for a given row?
  3. Q3Why do LAG/LEAD require an ORDER BY inside OVER()?
  4. Q4What does LAG(x, 2) return (offset)?
  5. Q5What does LAG(x, 1, 0) do (default for the first row)?
  6. Q6What value does LAG return for the first row of a partition with no default?
  7. Q7How does PARTITION BY change LAG/LEAD (reset per group)?
  8. Q8Write the formula for month-over-month growth % using LAG.
  9. Q9Why guard the LAG denominator with NULLIF in a growth %?
  10. Q10Difference between LAG and accessing the previous row via a self-join.
  11. Q11What is "period-over-period" analysis?
  12. Q12How to compute the gap (days) between consecutive orders per customer.
  13. Q13Can LAG/LEAD appear in WHERE directly? What's the workaround?
  14. Q14How does LEAD(x, 1) help compute "time to next event"?
  15. Q15Why must the ORDER BY be deterministic for LAG/LEAD to be meaningful?
  16. Q16What's the result type of LAG on a numeric column?
  17. Q17How to detect a change (current <> previous) with LAG.
  18. Q18How to flag rows where value increased vs the previous row.
  19. Q19Why might LAG over months need a gap-filled month series first?
  20. Q20How to compute first-vs-second order delta with LAG.
  21. Q21What does LEAD return for the last row of a partition (no default)?
  22. Q22How to compute a running difference (delta) series with LAG.
  23. Q23When to use LAG vs a window frame for "previous value".
  24. Q24How to compute % change vs the same month last year (LAG 12 on monthly series).
  25. Q25Name one analyst use each for LAG, LEAD, and period-over-period growth.

LAG BASICS

  1. Q26For each order per customer, show the previous order's net_total (LAG).
  2. Q27For each order per customer, show the previous order_date.
  3. Q28Monthly revenue with the previous month's revenue beside it.
  4. Q29For each product price-ordered, the previous product's price.
  5. Q30For each review per product, the previous review's rating.
  6. Q31For each call per agent, the previous call's duration.
  7. Q32For each shipment per courier, the previous delivery time.
  8. Q33For each pay_slip per employee, the previous month's net_salary.
  9. Q34For each order per store (by date), the previous order's value.
  10. Q35LAG with offset 2: the order two-before per customer.
  11. Q36LAG with default 0: previous net_total, 0 for first order.
  12. Q37Monthly signups with previous month's signups.
  13. Q38For each snapshot per (warehouse, product), the previous quantity.
  14. Q39For each customer's order, the date of their previous order.
  15. Q40Previous day's revenue beside each day's revenue.
  16. Q41For each ad spend per campaign, the previous spend amount.
  17. Q42For each ticket per agent, the previous ticket's created_date.
  18. Q43For each product per brand (price desc), the next-cheaper price (LAG on desc).
  19. Q44Previous week's order count beside each week.
  20. Q45For each member, the previous points_balance snapshot (if ordered by date).
  21. Q46LAG to show "prior status" of orders per customer over time.
  22. Q47Previous quarter revenue beside each quarter.
  23. Q48For each order line, the previous line's net_amount within the order.
  24. Q49Previous month's expenses per department.
  25. Q50For each customer, previous order value AND the one before (LAG 1 and LAG 2).

LEAD BASICS

  1. Q51For each order per customer, the NEXT order's net_total (LEAD).
  2. Q52For each order per customer, the next order_date.
  3. Q53Days until the next order per customer (LEAD on order_date - order_date).
  4. Q54For each review per product, the next review's rating.
  5. Q55For each call per customer, time until the next call.
  6. Q56Monthly revenue with the next month's revenue beside it.
  7. Q57For each product price-ordered, the next product's price.
  8. Q58For each shipment per courier, the next shipment's shipped_date.
  9. Q59LEAD with offset 2: the order two-after per customer.
  10. Q60LEAD with default: next net_total, NULL->0 for the last order.
  11. Q61For each page_view per session, the next view_timestamp (session step).
  12. Q62For each pay_slip per employee, the next month's net_salary.
  13. Q63Time gap to next ticket per agent.
  14. Q64For each snapshot per product, the next day's quantity.
  15. Q65For each customer's first order, when their second order happened (LEAD).
  16. Q66Next week's order count beside each week.
  17. Q67For each order per store, the next order's value.
  18. Q68Gap to next review per customer (engagement cadence).
  19. Q69For each campaign spend, the next spend amount and date.
  20. Q70Next quarter revenue beside each quarter.
  21. Q71For each member, the next points snapshot.
  22. Q72Time until next purchase per customer (churn signal).
  23. Q73For each order line, the next line's net_amount within the order.
  24. Q74Next day's revenue beside each day.
  25. Q75For each customer, next order value AND the one after (LEAD 1 and LEAD 2).

DELTAS & PERIOD-OVER-PERIOD

  1. Q76Month-over-month revenue growth % (LAG, NULLIF-guarded).
  2. Q77Day-over-day change in order count.
  3. Q78Gap in days between consecutive orders per customer.
  4. Q79First-vs-second order value delta per customer.
  5. Q80Detect price changes: rows where current price <> previous (price-ordered series).
  6. Q81Flag days where revenue > previous day by > 20%.
  7. Q82Week-over-week order growth % per store.
  8. Q83Quarter-over-quarter revenue change per region.
  9. Q84Change in net_salary vs previous month per employee.
  10. Q85Delta in points_balance vs previous snapshot per member.
  11. Q86Month-over-month signup growth %.
  12. Q87Difference in delivery time vs previous shipment per courier.
  13. Q88Web session duration: last view_timestamp - first via LEAD per session.
  14. Q89Same-month-last-year revenue compare (LAG 12 on monthly series).
  15. Q90Flag rating drops: review rating < previous review rating per product.
  16. Q91Detect stock-outs: snapshot quantity drops to 0 from a positive previous.
  17. Q92Order value delta vs previous order per customer (absolute + %).
  18. Q93Month-over-month expense change % per department.
  19. Q94Days between first and second order per customer (LEAD on first).
  20. Q95Flag months with negative MoM growth.
  21. Q96Consecutive-day revenue streak setup (current vs previous comparison).
  22. Q97Detect a >50% price jump vs previous (data-entry error flag).
  23. Q98Customer reactivation gap: longest gap between consecutive orders.
  24. Q99Period-over-period contribution: this month vs last month per category.
  25. Q100MoM revenue table per store: revenue, prev_revenue, growth %, up/down flag.

Combined ideas, multi-step thinking

CONCEPTUAL

  1. Q1Why partition before LAG for "previous order per customer"?
  2. Q2Growth % formula with LAG; why NULLIF guards division.
  3. Q3Why a gap-filled month series matters before LAG on monthly data.
  4. Q4LAG(x, n, default) - each argument's role.
  5. Q5How to compute "days since previous order" per customer.
  6. Q6How to detect a change-point (value differs from previous).
  7. Q7Sessionization idea: new session when gap to previous event > N minutes.
  8. Q8Why LEAD is natural for "time-to-next-event".
  9. Q9YoY compare: LAG 12 on a monthly series - requirements.
  10. Q10How to flag the first row of each partition (LAG IS NULL).
  11. Q11Compute both prior and next value in one query (LAG + LEAD).
  12. Q12Why filter on a LAG result must be in an outer query/CTE.
  13. Q13How to compute a delta and a % delta together.
  14. Q14Detecting streaks: same value as previous row (run-length idea).
  15. Q15Why deterministic ORDER BY (date, id) is needed for stable LAG.
  16. Q16Difference: gap to previous vs gap to next event.
  17. Q17How to handle missing periods (no order in a month) for growth.
  18. Q18Compute "previous non-null value" - limitation of plain LAG.
  19. Q19How to compute moving change (this - 3 rows ago) with LAG offset.
  20. Q20Sessionization: cumulative sum of "new session" flags = session id.
  21. Q21Why period-over-period needs aggregation to a grain first.
  22. Q22How to compute first-purchase to second-purchase latency.
  23. Q23Detect reactivations: gap to previous order > 90 days.
  24. Q24How to compare each region's month to its own previous month.
  25. Q25When LAG/LEAD beats a self-join (clarity + speed).

PERIOD-OVER-PERIOD

  1. Q26MoM revenue growth % per store (aggregate to month, LAG).
  2. Q27WoW order-count growth % per store.
  3. Q28QoQ revenue change per region.
  4. Q29YoY monthly revenue compare (LAG 12).
  5. Q30MoM signup growth % per region.
  6. Q31MoM expense change % per department.
  7. Q32Day-over-day revenue change (company-wide, gap-filled).
  8. Q33MoM net_salary change per employee.
  9. Q34MoM units-sold growth % per product.
  10. Q35Week-over-week active customers change.
  11. Q36MoM revenue growth % per category.
  12. Q37QoQ growth per store with up/down flag.
  13. Q38MoM refund change % per category.
  14. Q39MoM ad-spend change % per platform.
  15. Q40YoY quarterly revenue compare per region.
  16. Q41MoM review-count change per product.
  17. Q42MoM ticket-volume change per category.
  18. Q43MoM page-views change per device.
  19. Q44MoM AOV (avg order value) change per store.
  20. Q45MoM new-customer growth per city (via addresses).
  21. Q46MoM revenue contribution change per region (% of company).
  22. Q47MoM growth flagged where it turns negative.
  23. Q48Rolling 2-period change: this month vs 2 months ago (LAG 2).
  24. Q49MoM growth for the top-5 brands by revenue.
  25. Q50Build a MoM table per store: month, revenue, prev, growth %, flag.

GAP ANALYSIS

  1. Q51Days between consecutive orders per customer.
  2. Q52Average inter-order gap per customer (LAG then AVG).
  3. Q53Longest gap between orders per customer.
  4. Q54Customers with a gap > 90 days (reactivation candidates).
  5. Q55Time to second order per customer (first->second latency).
  6. Q56Gap to next review per customer (engagement cadence).
  7. Q57Time between consecutive calls per customer.
  8. Q58Gap between consecutive shipments per courier.
  9. Q59Days between consecutive snapshots per (warehouse, product).
  10. Q60Time between first and last order per customer (lifespan).
  11. Q61Gap between consecutive logins/page-views per session.
  12. Q62Customers whose most recent gap exceeds their average gap (slowing down).
  13. Q63Inter-purchase time distribution buckets per customer.
  14. Q64Gap to next ticket per agent (workload spacing).
  15. Q65Days since previous price change per product (price-ordered).
  16. Q66Gap between consecutive payments per order.
  17. Q67Median inter-order gap per region (LAG then percentile).
  18. Q68Customers with shrinking gaps (accelerating purchase frequency).
  19. Q69First-order to registration latency (orders vs registration_date).
  20. Q70Gap between consecutive promotions (start dates).
  21. Q71Time-to-next-order after a return (post-return behavior).
  22. Q72Largest single-day revenue jump per store (gap to previous day).
  23. Q73Customers dormant > 180 days (gap to today via LEAD/CURRENT_DATE).
  24. Q74Average gap between reviews per product.
  25. Q75Build a per-customer "purchase cadence" table: avg/min/max gap.

SESSIONIZATION & CHANGE DETECTION

  1. Q76Flag a new web session when gap to previous view > 30 minutes.
  2. Q77Assign session ids via cumulative sum of new-session flags.
  3. Q78Session duration per session (first to last view_timestamp).
  4. Q79Views per session (count within derived session id).
  5. Q80Detect price changes: rows where price <> previous price per product.
  6. Q81Detect > 50% price jumps (likely data-entry errors).
  7. Q82Detect order-status transitions over time per order (if status changes logged).
  8. Q83Detect tier upgrades/downgrades per customer over time.
  9. Q84Detect stock-outs: quantity drops to 0 from positive previous.
  10. Q85Detect rating drops vs previous review per product.
  11. Q86Flag revenue days that broke the previous record (running-max compare).
  12. Q87Detect reactivation events (gap > 90 days then an order).
  13. Q88Detect consecutive declining months (2+ negative MoM in a row).
  14. Q89Detect the first month a store crossed Rs1,00,000 revenue.
  15. Q90Detect churn signal: gap to next order is NULL (no next order).
  16. Q91Count distinct sessions per customer per day.
  17. Q92Average session length per device_type.
  18. Q93Detect rapid repeat orders (next order within 1 day).
  19. Q94Detect salary changes per employee across months.
  20. Q95Flag products whose price changed more than 3 times.
  21. Q96Detect a customer's longest active streak (consecutive months with orders).
  22. Q97Flag campaigns whose spend doubled vs previous period.
  23. Q98Detect inventory replenishment (quantity rises vs previous snapshot).
  24. Q99Sessionize calls: new "call burst" when gap > 1 hour per customer.
  25. Q100Build a sessionized web table: session_id, start, end, duration, view_count.

Interview grade, edge cases

CONCEPTUAL

  1. Q1Gaps-and-islands: how does (row_number - LAG-based group key) find consecutive runs?
  2. Q2Why MoM growth needs a gap-filled month spine (missing months break LAG).
  3. Q3YoY vs MoM: LAG 12 vs LAG 1 on a monthly grain - pitfalls.
  4. Q4Sessionization: new-session flag -> cumulative sum -> session id; why it works.
  5. Q5Churn signal: LEAD(order_date) IS NULL vs gap-to-today - which and why.
  6. Q6"Previous non-null value": why plain LAG fails on sparse data; the fix.
  7. Q7Detecting streaks of consecutive active months per customer.
  8. Q8Why ORDER BY (date, id) tie-break matters for LAG correctness.
  9. Q9Combining LAG with a moving average (Topic 17) for anomaly detection.
  10. Q10Period-over-period at two grains (store-month and region-month) in one query.
  11. Q11Why growth tables filter the first period (no prior) out or label it.
  12. Q12Reactivation cohorts: define via gap > 90 days then activity.
  13. Q13First-touch to conversion latency via LEAD across event types.
  14. Q14Detect monotonic decline (N consecutive negative deltas).
  15. Q15Why "consecutive days" islands need a date spine, not just order rows.
  16. Q16Compute time-in-state (status durations) using LEAD on a state log.
  17. Q17Run-length encoding of a status series with gaps-and-islands.
  18. Q18Why LAG-based change detection beats comparing to a stored "previous" column.
  19. Q19Compute "days to churn" (last order to a cutoff) with LEAD/CURRENT_DATE.
  20. Q20Multi-offset comparison (vs 1, 3, 12 periods ago) in one query.
  21. Q21Detecting price-change events and the magnitude of each change.
  22. Q22Build a retention curve input (active in month N after signup) with LAG/LEAD.
  23. Q23Why sessionization thresholds belong in a CTE for tunability.
  24. Q24Cumulative streak length that resets on a break.
  25. Q25Combine LAG growth % with NTILE to bucket "fastest-growing" stores.

GROWTH TABLES

  1. Q26SCENARIO: CFO wants a MoM revenue growth table per region (revenue, prev, growth %, flag).
  2. Q27YoY monthly revenue growth % per store (LAG 12, gap-filled).
  3. Q28QoQ revenue growth per region with up/down arrows.
  4. Q29MoM AOV growth per store.
  5. Q30MoM units growth % for the top-10 products by revenue.
  6. Q31Compare growth vs 1, 3, and 12 months ago in one row.
  7. Q32MoM new-customer growth per acquisition city.
  8. Q33MoM refund-rate change per category.
  9. Q34WoW active-customer growth per region.
  10. Q35MoM contribution shift: each category's %-of-company change.
  11. Q36MoM gross-margin growth per brand.
  12. Q37Fastest-growing stores: rank by latest MoM growth % (combine with Topic 16 rank).
  13. Q38MoM ad-spend efficiency change per platform.
  14. Q39MoM ticket-volume growth per priority.
  15. Q40Detect the first month each store exceeded its prior peak revenue.
  16. Q41MoM page-view growth per device, gap-filled.
  17. Q42Compounded 3-month growth setup (chain of MoM deltas).
  18. Q43MoM net_salary cost growth per department.
  19. Q44Seasonality check: same-month-last-year compare per region.
  20. Q45MoM growth for new vs returning customer revenue (split then LAG).
  21. Q46Negative-growth alert: stores with 2+ consecutive declining months.
  22. Q47MoM revenue growth with both absolute delta and % delta.
  23. Q48Quarter-over-quarter growth for the top-5 categories.
  24. Q49MoM growth indexed: first month = 100, each month relative.
  25. Q50Full growth pack: per store-month revenue, prev, MoM %, YoY %, flag.

GAPS-AND-ISLANDS

  1. Q51SCENARIO: Find each customer's longest streak of consecutive active months.
  2. Q52Consecutive-day order streaks per store.
  3. Q53Identify "islands" of months where a store had revenue (vs gaps).
  4. Q54Longest run of months a product sold continuously.
  5. Q55Consecutive weeks a customer placed at least one order.
  6. Q56Identify gaps (missing months) in each store's revenue timeline.
  7. Q57Run-length of consecutive 5-star reviews per product.
  8. Q58Streak of consecutive on-time deliveries per courier.
  9. Q59Consecutive months an employee's salary stayed unchanged.
  10. Q60Identify continuous in-stock periods per (warehouse, product).
  11. Q61Longest consecutive-day login streak per customer (web_events).
  12. Q62Islands of consecutive profitable months per store.
  13. Q63Consecutive price-stable periods per product (no price change).
  14. Q64Streak of months with positive MoM growth per region.
  15. Q65Group consecutive same-status order runs per customer.
  16. Q66Find the start/end dates of each active island per customer.
  17. Q67Longest gap (out-of-stock island) per product.
  18. Q68Consecutive quarters a brand grew revenue.
  19. Q69Identify reactivation islands (active -> gap -> active) per customer.
  20. Q70Streak of consecutive weeks a campaign ran (spend present).
  21. Q71Run-length encode a customer's tier history.
  22. Q72Consecutive days revenue beat the previous day (winning streak).
  23. Q73Islands of consecutive months with refunds per customer.
  24. Q74Longest consecutive-month payroll run per employee.
  25. Q75Build an islands table: customer, island_start, island_end, length.

CHURN, SESSIONS & CHANGE DETECTION

  1. Q76SCENARIO: Flag churn-risk customers: no order in the last 90 days (gap to today).
  2. Q77Reactivation events: order after a > 90-day gap.
  3. Q78Time-to-second-order per customer (first->second latency).
  4. Q79Sessionize web_events: new session when gap > 30 min; output session ids.
  5. Q80Session duration and view count per derived session.
  6. Q81Detect price-change events with old->new and % change per product.
  7. Q82Detect > 50% price jumps (data-entry error candidates).
  8. Q83Detect tier upgrades and downgrades per customer over time.
  9. Q84Detect stock-outs (positive -> 0) and recoveries (0 -> positive).
  10. Q85Detect rating drops (review < previous) per product.
  11. Q86Churn cohort: customers whose last order is in each month.
  12. Q87Detect consecutive declining-revenue months per store (alert).
  13. Q88Average session length per device and per day.
  14. Q89Detect rapid repeat purchases (next order within 24h).
  15. Q90Detect salary changes and their magnitude per employee.
  16. Q91Time-in-status durations from a status log (LEAD on timestamps).
  17. Q92Detect customers slowing down: latest gap > 2x their median gap.
  18. Q93First-vs-last order value change per customer (loyalty value trend).
  19. Q94Detect campaigns whose spend doubled period-over-period.
  20. Q95Web funnel step timing: time between page -> cart -> checkout via LEAD.
  21. Q96Detect the month a customer's spend peaked then declined.
  22. Q97Sessionize calls into bursts (gap > 1h) and count bursts per customer.
  23. Q98Detect inventory replenishment events and their size.
  24. Q99Build a churn dashboard: customer, last_order, days_since, churn_flag, prior_gap.
  25. Q100Full change-log: per product, every price change with date, old, new, %, rank of change.

Production scenarios, optimisation

CONCEPTUAL

  1. Q1Architect a growth dashboard comparing vs 1/3/12 periods in one query.
  2. Q2Cohort retention matrix inputs purely via LAG/LEAD + date math.
  3. Q3Gaps-and-islands at scale: the (rn - dense group) trick and its cost.
  4. Q4Sessionization pipeline: threshold -> flag -> cumulative session id -> aggregate.
  5. Q5Anomaly = (value - trailing MA) / trailing stddev; combine Day-17 frame + LAG.
  6. Q6Robust MoM on sparse data: date spine + LAG + COALESCE strategy.
  7. Q7"Previous non-null" via a running max of (value's row when not null) - pattern.
  8. Q8Churn vs reactivation definitions and how LAG/LEAD encode each.
  9. Q9Multi-grain period-over-period (SKU/brand/category) without N queries.
  10. Q10Time-in-state and state-transition matrices from a log via LEAD.
  11. Q11Why compounded growth needs careful chaining (product of (1+gi)).
  12. Q12Funnel timing: median time between steps via LEAD + percentile.
  13. Q13Detect monotonic trends (N consecutive same-sign deltas) generically.
  14. Q14Reproducible cohort timelines across refreshes (anchor on first activity).
  15. Q15Sessionization tuning: choosing the gap threshold from the gap distribution.
  16. Q16Why LAG-based change logs beat trigger-based audit for analytics.
  17. Q17Period-over-period with mixed calendars (fiscal vs ISO week).
  18. Q18Detecting seasonality via YoY LAG and de-seasonalizing.
  19. Q19Combine LAG growth with NTILE to rank fastest/slowest movers.
  20. Q20Building a "days to next purchase" survival-style table.
  21. Q21Streak analytics: longest/current/average streak per entity.
  22. Q22Why sessionization + funnel must share one ordered event stream.
  23. Q23Anomaly suppression: ignore deltas within +/- of a trailing band.
  24. Q24Cohort decay curve from retention deltas.
  25. Q25When to precompute these in an MV (Topic 25) vs on-the-fly.

GROWTH & RETENTION SYSTEMS

  1. Q26SCENARIO: Build a per-store growth board: MoM %, QoQ %, YoY %, and a trend flag.
  2. Q27Signup-cohort retention: % of each month's cohort active in months 1-6 (LAG/LEAD + date math).
  3. Q28Multi-offset compare (1/3/12 months ago) per region in one row.
  4. Q29Compounded 6-month growth per store (chain of MoM).
  5. Q30New vs returning revenue MoM growth (split then LAG).
  6. Q31Fastest-growing brands: latest MoM % then NTILE into growth tiers.
  7. Q32Retention curve per acquisition channel (first order channel proxy).
  8. Q33YoY same-month compare per category with seasonality flag.
  9. Q34Cohort revenue indexed to cohort month 0 = 100.
  10. Q35MoM contribution shift: category %-of-company change over time.
  11. Q36Reactivation rate per month (reactivated / churned base).
  12. Q37Customer lifetime value trajectory: cumulative spend with MoM deltas.
  13. Q38Detect decelerating stores: 3 consecutive declining MoM %.
  14. Q39Region revenue YoY with both absolute and % change.
  15. Q40AOV trend per store: MoM AOV growth with up/down streak.
  16. Q41Per-cohort "months to second purchase" distribution.
  17. Q42Growth attribution: which categories drove company MoM change.
  18. Q43Rolling retention: % active in trailing 30/60/90 days per cohort.
  19. Q44MoM churn count and reactivation count side by side.
  20. Q45Top-10 fastest-growing products by YoY units.
  21. Q46Net revenue retention proxy per cohort (this period / prior period spend).
  22. Q47Detect first month a store turned profitable then stayed.
  23. Q48Compounded category growth ranked across the catalog.
  24. Q49Per-customer spend momentum (recent 3-mo vs prior 3-mo).
  25. Q50Full growth+retention pack per region-month (8 metrics).

GAPS-AND-ISLANDS & SESSIONS AT SCALE

  1. Q51SCENARIO: Sessionize all web_events (gap > 30 min) and emit session_id, start, end, duration, views.
  2. Q52Longest active-month streak per customer across 50k customers.
  3. Q53Continuous in-stock islands per (warehouse, product) with start/end.
  4. Q54Consecutive-day order streaks per store with streak length.
  5. Q55Run-length encode each customer's tier history into intervals.
  6. Q56Funnel timing: median minutes page->cart->checkout via LEAD per session.
  7. Q57Identify reactivation islands (active->gap->active) and count per customer.
  8. Q58Consecutive profitable months per store with longest run.
  9. Q59Out-of-stock islands and their durations per product.
  10. Q60Group consecutive same-status order runs and their spans.
  11. Q61Login streaks per customer (consecutive days) from web_events.
  12. Q62Price-stability islands per product (no change) with lengths.
  13. Q63Sessionize calls into bursts (gap > 1h) and average burst size.
  14. Q64Continuous-growth islands (consecutive positive MoM) per region.
  15. Q65Time-in-status durations from an order status log (LEAD).
  16. Q66Longest consecutive 5-star review run per product.
  17. Q67Per-session funnel completion flag (reached checkout?).
  18. Q68Streaks of consecutive on-time deliveries per courier.
  19. Q69Identify the dominant session length band per device.
  20. Q70Consecutive payroll months per employee (employment continuity).
  21. Q71Bounce sessions (1 view) vs engaged sessions (>=5 views) per day.
  22. Q72Reactivation latency distribution (gap length before reactivation).
  23. Q73Longest winning streak (revenue beats prior day) per store.
  24. Q74Per-customer cadence islands: stable-frequency vs erratic periods.
  25. Q75Build a sessions fact table from web_events (one row per session).

ANOMALY & EXECUTIVE DASHBOARDS

  1. Q76SCENARIO: Daily revenue anomaly board: value, 7-day MA (Topic 17), MoM, z-score, flag.
  2. Q77Detect days where revenue deviates > 2sigma from trailing 30-day mean.
  3. Q78Price-change log: every product price change with old/new/%/rank and date.
  4. Q79Churn dashboard: customer, last_order, days_since, prior_gap, churn_flag.
  5. Q80Retention heatmap inputs: cohort x month-since with retained %.
  6. Q81Sessionized funnel dashboard: sessions, cart rate, checkout rate, median step time.
  7. Q82Store momentum board: MoM %, QoQ %, YoY %, streak, percentile of growth.
  8. Q83Demand-spike detector: product days where units > 3x trailing-7 average.
  9. Q84Reactivation dashboard per month: churned, reactivated, net.
  10. Q85Delivery-degradation alert: courier whose delivery time rose 3 months running.
  11. Q86Inventory volatility board: stock change deltas and stock-out events per product.
  12. Q87Customer health trend: spend momentum + recency + churn risk in one row.
  13. Q88Category growth waterfall MoM (who gained/lost share).
  14. Q89Agent performance trend: resolved/day MoM with streaks.
  15. Q90Anomalous orders: value > customer's trailing-mean by large margin.
  16. Q91Web engagement board: sessions/day, avg duration, MoM growth per device.
  17. Q92Refund-spike detector per category (MoM refund % jump).
  18. Q93Seasonality board: YoY same-month index per region.
  19. Q94Fastest decelerating SKUs (steepest negative YoY units).
  20. Q95Net-new-customer board with MoM and YoY growth.
  21. Q96Executive trend pack per region-month: revenue, MoM, YoY, 3-mo MA, anomaly flag.
  22. Q97First-to-second purchase conversion board per cohort.
  23. Q98Price-war detector: clusters of frequent price changes per brand.
  24. Q99Sessionized conversion funnel with drop-off % per step.
  25. Q100End-to-end exec board: growth + retention + anomaly + churn, per region-month.