TheShiraverseSELECT * TheShiraverseCurriculumPracticeKahaniFAQGet StartedPlaygroundNukteTheShiraverse ↗
Practice › Topic 23

Date, Time and Time Series: 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 DATE_TRUNC('month', d) do?
  2. Q2What does EXTRACT(YEAR FROM d) return?
  3. Q3Difference between a DATE and a TIMESTAMP in PostgreSQL.
  4. Q4Why are order_date / delivered_date stored as DATE (no time) in RetailMart?
  5. Q5What does date - date return (and in what unit)?
  6. Q6What does date + integer return?
  7. Q7What does an INTERVAL represent, e.g. INTERVAL '7 days'?
  8. Q8What does generate_series(a, b, step) produce?
  9. Q9How does generate_series build a date dimension (one row per day)?
  10. Q10What is "gap-filling" and why LEFT JOIN a generated date series?
  11. Q11What does to_char(d, 'YYYY-MM') produce?
  12. Q12What does EXTRACT(DOW FROM d) return (which day is 0)?
  13. Q13Difference between DOW and ISODOW.
  14. Q14What is an ISO week, and why can it cross a calendar-year boundary?
  15. Q15What weekday does date_trunc('week', d) anchor to?
  16. Q16How do you get the first day of a date's month?
  17. Q17How do you get the last day of a date's month?
  18. Q18What does age(d1, d2) compute vs plain d1 - d2?
  19. Q19What do now() and current_date return?
  20. Q20What does AT TIME ZONE do to a timestamp?
  21. Q21Why do hour-of-day analyses need a TIMESTAMP, not a DATE column?
  22. Q22Which RetailMart columns carry real time-of-day (name three)?
  23. Q23What is a fiscal quarter, and how can it differ from a calendar quarter?
  24. Q24How do you bucket timestamps into 15-minute slots (concept)?
  25. Q25What does make_date(y, m, d) build?

TRUNC / EXTRACT / FORMAT

  1. Q26Orders grouped by month (date_trunc) with counts.
  2. Q27Orders grouped by year with counts.
  3. Q28Revenue per calendar quarter (date_trunc('quarter', order_date)).
  4. Q29Order count per day-of-week name (to_char).
  5. Q30Order count per month name (to_char 'Month').
  6. Q31Year and month as two separate columns from order_date.
  7. Q32Revenue per week (date_trunc('week')).
  8. Q33Count of customers registered per year.
  9. Q34Page_view count per hour-of-day (EXTRACT HOUR from view_timestamp).
  10. Q35Calls per hour-of-day (call_start_time).
  11. Q36Tickets opened per month (created_date).
  12. Q37Orders per quarter for 2025 only.
  13. Q38Average net_total per month for 2025.
  14. Q39Revenue by 'YYYY-MM' label (to_char).
  15. Q40Orders on weekends vs weekdays (DOW filter).
  16. Q41Order count per ISO week of 2025 (to_char 'IYYY-IW').
  17. Q42Earliest and latest order_date in the data.
  18. Q43Delivery days per order (delivered_date - order_date).
  19. Q44Orders delivered in 0-2 days vs 3+ days (date-diff buckets).
  20. Q45Month name of each customer's registration.
  21. Q46Revenue per half-year (H1/H2) using EXTRACT(month).
  22. Q47Count of reviews per month.
  23. Q48Ad spend per month per platform.
  24. Q49Parse pay-slip period: to_date(salary_month||' '||salary_year,'FMMonth YYYY').
  25. Q50Orders per day-of-month (1..31) distribution.

GENERATE_SERIES & GAP-FILLING

  1. Q51Generate one row per day for January 2025.
  2. Q52Generate one row per month for all of 2025.
  3. Q53Generate a 2025 date spine with date, dow name, week_num, month_name.
  4. Q54Daily order counts for Jan 2025, zero on days with no orders (gap-fill).
  5. Q55Daily revenue for one month with zeros on missing days.
  6. Q56Monthly revenue for 2025 with zeros for months that had none.
  7. Q57Generate the last 90 days ending at max(order_date).
  8. Q58Complete daily revenue for the last 90 days (one row per day, zero when none).
  9. Q59Weekly order counts for 2025 with missing weeks shown as zero.
  10. Q60Generate hours 0..23 and LEFT JOIN page_view counts (zero-fill hours).
  11. Q61Generate every Monday in 2025.
  12. Q62Build the 2025 quarter-start dates.
  13. Q63Generate a date spine with a fiscal_quarter column (April-start concept).
  14. Q64Gap-fill a sparse weekly metric (a week with no rows shows 0).
  15. Q65Daily new-customer counts with zero-filled days.
  16. Q66Per store: daily order counts gap-filled for one month.
  17. Q67Generate the 12 month-start dates of 2025.
  18. Q68LEFT JOIN a date series to returns for daily refund totals (zero-filled).
  19. Q69Generate 15-minute slots across one day (interval '15 min').
  20. Q70Generate week numbers 1..52 for labelling.
  21. Q71Calendar of 2025 with an is_weekend flag.
  22. Q72Day series marking Indian weekends (Sat/Sun).
  23. Q73Per day in the last 30: distinct customers ordering (zero-filled).
  24. Q74Build a "first 30 days" date series from one customer's registration.
  25. Q75Daily cumulative registrations across 2025 (series + running sum, Topic 17).

INTERVALS, TIMEZONES & BUCKETS

  1. Q76Add 7 days to each order_date (order_date + INTERVAL '7 days').
  2. Q77Orders placed in the last 30 days of the data window.
  3. Q78Customers registered more than 365 days before their first order.
  4. Q79Convert view_timestamp from UTC to IST (AT TIME ZONE).
  5. Q80Hour-of-day distribution of page_views in IST.
  6. Q81Peak shopping hour from page_views (IST) - top hour by count.
  7. Q82Count calls in each hour-of-day (call_start_time).
  8. Q8315-minute bucket of page_views to find the busiest slot.
  9. Q84Time-to-resolution in hours for tickets (resolved_date - created_date).
  10. Q85Tickets resolved within 24 hours vs longer.
  11. Q86Orders shipped within 1 day of order (shipped_date - order_date <= 1).
  12. Q87Average delivery interval per region in days.
  13. Q88Morning/afternoon/evening buckets of page_views (needs a timestamp).
  14. Q89Days since last order for each customer (current_date - max order_date).
  15. Q90Customer tenure in years (age(current_date, registration_date)).
  16. Q91Revenue per ISO week with correct year-week boundary (to_char 'IYYY-IW').
  17. Q92Orders per fiscal quarter (fiscal year starting April).
  18. Q93Month-over-month revenue growth (date_trunc + LAG, Topic 18).
  19. Q94Rolling 7-day order count over a daily series (window frame, Topic 17).
  20. Q95First and last activity date per customer (orders + page_views).
  21. Q96Events per weekday x hour heatmap (page_views).
  22. Q97Median order value per month (Topic 22 percentile + date_trunc).
  23. Q98Latest order per customer with days-since (DISTINCT ON, Topic 22 + date diff).
  24. Q99Daily revenue gap-filled for 90 days with a 7-day moving average (Topic 17).
  25. Q100Executive time-series strip: monthly orders, revenue, and MoM % for 2025.

Combined ideas, multi-step thinking

CONCEPTUAL

  1. Q1Why prefer date_trunc over to_char for grouping (returns a date, sorts right)?
  2. Q2Pitfall of grouping by to_char(d,'Month') (alphabetical sort) - and the fix.
  3. Q3Why gap-fill with a generated calendar instead of trusting present rows?
  4. Q4LEFT JOIN calendar -> fact: which side drives, and why COALESCE(...,0)?
  5. Q5ISO week vs calendar week - when the year-week label matters.
  6. Q6Why can EXTRACT(week) differ from to_char('IW') at year boundaries?
  7. Q7Fiscal year: shifting months by an offset to start in April.
  8. Q8Timezone: storing UTC, displaying IST - the two AT TIME ZONE steps explained.
  9. Q9Difference: ts AT TIME ZONE 'UTC' vs ts AT TIME ZONE 'Asia/Kolkata'.
  10. Q10Why DATE columns cannot answer "peak hour" questions.
  11. Q11Interval ambiguity: INTERVAL '1 month' vs '30 days'.
  12. Q12generate_series with timestamps and an interval step.
  13. Q13Building arbitrary-width buckets (15-min, 4-hour) via epoch math.
  14. Q14Why a date-dimension table speeds up repeated reporting (Topic 20 indexing).
  15. Q15One calendar spine, many facts: the multi-LEFT-JOIN pattern.
  16. Q16Detecting activity gaps/islands (consecutive-day runs) - the idea.
  17. Q17Rolling windows: RANGE vs ROWS frames over time (Topic 17).
  18. Q18Why a 7-day moving average needs a gap-filled daily series first.
  19. Q19Cohort timelines: how to anchor "day 0" per customer.
  20. Q20Computing "days since previous order" with LAG (Topic 18).
  21. Q21Month boundaries: date_trunc('month') + interval to reach month-end.
  22. Q22Handling DST / ambiguous local times (concept; note IST has no DST).
  23. Q23Why age() can surprise across months of different lengths.
  24. Q24Controlling the week anchor: ISO Monday-start vs Sunday-start.
  25. Q25When to precompute a time-series in a materialized view (Topic 25 preview).

BUCKETING & AGGREGATION

  1. Q26Monthly revenue, order count, and AOV for 2025.
  2. Q27Quarterly revenue per region (date_trunc + join).
  3. Q28Weekly order counts per store (ISO week).
  4. Q29Day-of-week x store revenue matrix (pivot, Topic 21).
  5. Q30Hour-of-day page_view counts per device type.
  6. Q31Monthly new vs returning customer counts.
  7. Q32Revenue per fiscal quarter (April-start fiscal year).
  8. Q33Month-name-labelled revenue ordered by month number.
  9. Q34Orders per ISO week handling the 2025-12 / 2026-01 boundary correctly.
  10. Q35Per region: monthly revenue for 2025 as a tidy long table.
  11. Q36Average delivery days per month (trend).
  12. Q37Tickets opened per weekday x priority (pivot).
  13. Q38Per hour-of-day: call volume and median call duration (Topic 22).
  14. Q39Registrations per month with a cumulative running total (Topic 17).
  15. Q40Daily revenue with a 7-day moving average (gap-filled series + window).
  16. Q41Monthly refund totals and refund rate.
  17. Q42Page_views per 15-minute slot; busiest 10 slots.
  18. Q43Revenue by half-year per region.
  19. Q44Orders per month split into weekday vs weekend (FILTER, Topic 21).
  20. Q45Per category: monthly units sold for 2025 (pivot by month).
  21. Q46Average basket size per quarter.
  22. Q47Median order value per month per region (Topic 22).
  23. Q48Ad spend vs revenue per month (two facts on one month spine).
  24. Q49Seasonality: average revenue per calendar month across all years.
  25. Q50Year-over-year monthly revenue (same month, prior year) with LAG.

GENERATE_SERIES, SPINES & GAP-FILL

  1. Q51Build a 2024-2026 daily spine with dow, iso_week, month, quarter, is_weekend.
  2. Q52Complete daily revenue for the last 90 days (zero-filled) - the lab.
  3. Q53Per store: complete daily order counts for one month (spine x stores, zero-fill).
  4. Q54Weekly revenue for 2025, every ISO week present even if zero.
  5. Q55Monthly revenue per region with every region x month present (cross join).
  6. Q56Hour-of-day spine (0..23) x device, zero-filled page_view counts.
  7. Q57Gap-fill a sparse weekly metric and compute WoW change (LAG).
  8. Q58First-30-days cohort timeline per customer (registration + series 0..29).
  9. Q59Daily active customers for 90 days (zero-filled) with 7-day moving avg.
  10. Q60Calendar spine joined to orders AND returns AND payments (multi-fact).
  11. Q61Generate a quarter spine 2024-2026 and LEFT JOIN revenue.
  12. Q62New-customer daily series with a cumulative running total.
  13. Q6315-minute spine across a day; join page_views; find the peak slot per device.
  14. Q64Per region daily revenue spine for a month; flag zero-revenue days.
  15. Q65Monthly spine with revenue and zero-safe MoM growth %.
  16. Q66Build a fiscal-calendar spine (fiscal_year, fiscal_quarter) 2024-2026.
  17. Q67Cohort retention spine: months-since-registration 0..11 per cohort.
  18. Q68Generate week-start dates for 2025 and bucket orders into them.
  19. Q69Daily refund totals zero-filled with a 14-day moving average.
  20. Q70Detect days with zero orders per store (via spine anti-join).
  21. Q71Per customer: a 12-month activity timeline from their first order.
  22. Q72Build an hour x weekday heatmap spine and fill page_view counts.
  23. Q73Generate a 2020-2030 date dimension with fiscal_quarter and week_num.
  24. Q74Calendar spine to compute "active days in month" per store.
  25. Q75Gap-and-island: longest run of consecutive days a store had orders.

TIMEZONES, INTERVALS & TIME-SERIES

  1. Q76Convert all page_view timestamps to IST and bucket by hour.
  2. Q77Peak shopping hour overall and per device (IST).
  3. Q78Calls per IST hour with median duration per hour (Topic 22).
  4. Q79Time-to-ship and time-to-deliver intervals per order, averaged per region.
  5. Q80% of orders delivered within SLA (<=3 days) per month.
  6. Q81% of tickets resolved within 24h per priority per month.
  7. Q82Customer tenure buckets (age) cross-tabbed with tier.
  8. Q83Days-between-orders per customer (LAG) and its monthly trend.
  9. Q84MoM and YoY revenue growth per region (two LAGs).
  10. Q85Rolling 28-day revenue per region (window RANGE frame).
  11. Q86Inter-arrival time of page_views per session (LAG on timestamp).
  12. Q87Median delivery interval per region per quarter (Topic 22 + date).
  13. Q88Revenue per ISO week with year-week label, correct across 2025/2026.
  14. Q89Busiest 15-minute window of the week (weekday x slot).
  15. Q90First-purchase latency: days from registration to first order, distribution.
  16. Q91Monthly cohort sizes and their month-1 retention.
  17. Q92Hour-of-day revenue proxy via page_views->orders timing (concept + join).
  18. Q93Time-bucketed funnel: events per stage per hour (web_events).
  19. Q94Daily revenue anomalies: days beyond P95 of the daily distribution (Topic 22).
  20. Q95Seasonal index: each month's revenue / its yearly average.
  21. Q96Latest activity timestamp per customer across orders + views (GREATEST/DISTINCT ON).
  22. Q97Rolling 7-day active-customer count (gap-filled + window).
  23. Q98Month-end vs month-start revenue (date_trunc boundaries).
  24. Q99Per region monthly revenue pivot (region x month) with MoM in cells.
  25. Q100Exec time-series dashboard: monthly orders, revenue, AOV, MoM%, YoY% for 2025.

Interview grade, edge cases

CONCEPTUAL

  1. Q1Designing a reusable date-dimension table: columns, grain, indexing (Topic 20).
  2. Q2Calendar spine via generate_series vs recursive generation - why the spine wins.
  3. Q3Gap-and-island detection: the row_number-difference trick (Topic 16).
  4. Q4RANGE vs ROWS time frames and their effect on moving aggregates (Topic 17).
  5. Q5Correct ISO-week-year reporting across the Dec/Jan boundary.
  6. Q6Fiscal calendar: generating fiscal_year/quarter/period with a month offset.
  7. Q7Timezone correctness: UTC storage, multi-zone reporting, DST awareness.
  8. Q8Why "peak hour" needs timestamp columns, and which RetailMart sources qualify.
  9. Q9Building N-minute buckets via epoch floor - the generalized formula.
  10. Q10Cohort analysis foundations: anchor date, period index, triangle shape (Topic 27 preview).
  11. Q11Moving average over a sparse series - why gap-fill must come first.
  12. Q12Computing MoM / YoY safely with LAG over a complete spine.
  13. Q13Missing-period growth %: zero vs NULL semantics.
  14. Q14Detecting activity streaks (consecutive days) and breaks.
  15. Q15Indexing a timestamp column for range-scan dashboards (Days 19-20).
  16. Q16Why date_trunc on an indexed column can prevent index use - expression-index fix.
  17. Q17Sessionization: splitting events into sessions by inactivity gap (LAG).
  18. Q18Time-weighted metrics (e.g. average inventory over time) - the concept.
  19. Q19Calendar-heatmap data shape (weekday x hour) and how to build it.
  20. Q20Backfilling a daily metric idempotently (Topic 25/26 preview).
  21. Q21Median vs mean for delivery-time SLAs over time (Topic 22 tie-in).
  22. Q22Choosing the spine grain (day/week/month) for a given report.
  23. Q23Pitfalls of EXTRACT(epoch) for interval lengths across DST.
  24. Q24Why a materialized daily-revenue table helps dashboards (Topic 25 preview).
  25. Q25Designing an "as-of" join against a date spine (point-in-time state).

TIME-SERIES REPORTS

  1. Q26Complete daily revenue last 90 days + 7-day and 28-day moving averages.
  2. Q27Per region monthly revenue with MoM% and YoY% (complete spine).
  3. Q28Gap-and-island: longest consecutive-day ordering streak per store.
  4. Q29Sessionize page_views (30-min inactivity) and count sessions per customer.
  5. Q30Daily active customers with 7-day moving average and WoW% (gap-filled).
  6. Q31ISO-weekly revenue 2024-2026 with correct boundaries, ranked weeks.
  7. Q32Fiscal-quarter revenue per region (April start) with QoQ growth.
  8. Q33Hour x weekday revenue-proxy heatmap from page_views (busiest cells).
  9. Q34Rolling 28-day retention: active in window / base (window RANGE).
  10. Q35Median + P95 delivery days per month (Topic 22) - trend.
  11. Q36New vs returning revenue split per month (first-order flag).
  12. Q37Cohort triangle: monthly cohorts x months-since for order counts.
  13. Q38Peak 15-minute slot per weekday with order/view counts.
  14. Q39Daily revenue anomalies beyond P95 of the daily distribution (Topic 22).
  15. Q40Seasonal-lite: month index vs trailing-12-month average.
  16. Q41Time-to-first-order distribution per registration cohort (median, P90).
  17. Q42Inter-order gap distribution per tier (LAG + percentile).
  18. Q43Refund rate per ISO week with a 4-week moving average.
  19. Q44Ticket SLA attainment per priority per month (resolved within target).
  20. Q45Month-end inventory snapshot value per warehouse over time (latest in month).
  21. Q46Revenue contribution by hour-of-day (IST) as % of daily total.
  22. Q47YoY same-week comparison (ISO week) per region.
  23. Q48Active-days-per-month per customer and its trend.
  24. Q49Complete store x month revenue matrix (zero-filled) + row/col totals.
  25. Q50Detect "dormant then reactivated" customers (gap > 90 days then ordered).

CALENDAR DIMENSIONS & GAP-FILL ENGINES

  1. Q51Build a 2020-2030 date dimension: date, dow, iso_dow, week, iso_week, month, month_name, quarter, fiscal_year, fiscal_quarter, is_weekend, is_month_end.
  2. Q52Use the dimension to gap-fill daily revenue across all stores.
  3. Q53Cross-join spine x regions to guarantee complete region x month revenue.
  4. Q54Cohort spine (cohort_month x period_index 0..11) joined to order facts.
  5. Q55Fiscal-period spine and revenue per fiscal period with QoQ.
  6. Q56Hour-of-day x device spine, zero-filled, with median duration per cell (Topic 22).
  7. Q5715-minute spine for one week; join page_views; rank slots.
  8. Q58As-of join: for each month-end, the latest inventory snapshot per SKU (Topic 22 DISTINCT ON).
  9. Q59Daily spine with multiple facts (orders, returns, payments) in one wide row.
  10. Q60Week spine 2024-2026 with WoW and 4-week moving avg for revenue.
  11. Q61Gap-fill + carry-forward last known value (LOCF) for a sparse metric (window).
  12. Q62Business-day calendar (exclude weekends) and count business days to deliver.
  13. Q63Month spine with seasonal index (month / annual mean).
  14. Q64Streaks: classify each customer's longest active streak via gap-and-island.
  15. Q65Reactivation spine: months-since-last-order per customer per month.
  16. Q66Calendar heatmap dataset: weekday x hour counts for page_views.
  17. Q67Per store: complete daily series + flag zero-revenue and record-high days.
  18. Q68Fiscal-year-to-date cumulative revenue per region (running sum, Topic 17).
  19. Q69Rolling 12-month revenue (trailing window) per region.
  20. Q70Date spine to compute customer "age at order" cohorts.
  21. Q71As-of price: most-recent selling price per product at each month-end (Topic 22).
  22. Q72Spine-driven SLA: % delivered within 3 days per day, 30-day moving average.
  23. Q73Detect missing daily snapshots per warehouse (expected vs present).
  24. Q74First/last activity per customer from a unified event spine.
  25. Q75Reusable "last 90 days" parameterized spine with three KPIs on it.

PRODUCTION TIME-SERIES SYSTEMS

  1. Q76Daily revenue dashboard: 90-day complete series, 7/28-day MA, MoM%, anomalies (P95).
  2. Q77Cohort retention matrix (monthly cohorts x 12 periods) with retention %.
  3. Q78Region growth board: monthly revenue, MoM%, YoY%, rolling-12m, rank.
  4. Q79Delivery SLA monitor: median/P95 delivery days per month + breach % + trend.
  5. Q80Sessionization pipeline: sessions per customer, median session length, bounce proxy.
  6. Q81Peak-hour ops report: busiest 15-min slots per weekday with a staffing hint.
  7. Q82Support SLA time-series: resolution median/P95 per priority per month + attainment.
  8. Q83Reactivation funnel: dormant (>90d) -> reactivated counts per month.
  9. Q84Seasonal index report per category (month / annual mean) for planning.
  10. Q85Fiscal dashboard: FYTD revenue, QoQ, vs prior-FY same period, per region.
  11. Q86Inventory-over-time: month-end value per warehouse + days-of-cover trend.
  12. Q87New vs returning revenue time-series with contribution % per month.
  13. Q88Anomaly detector: daily revenue beyond regional P95/P99 (Topic 22) flagged.
  14. Q89Heatmap export: weekday x hour activity, normalized per row.
  15. Q90Pricing time-series: per product as-of monthly price + drift flags.
  16. Q91Customer lifecycle timeline: registration -> first order -> latest order spans.
  17. Q92Rolling retention: 28-day active / 28-day-prior base, per region trend.
  18. Q93Time-to-deliver funnel by stage (order->ship->deliver) median per month.
  19. Q94Year-week revenue with correct ISO boundaries + YoY same-week.
  20. Q95Complete store x month revenue matrix with totals + zero-day diagnostics.
  21. Q96"Morning report": yesterday vs trailing-28-day median per region (as-of).
  22. Q97Cohort LTV-over-time: cumulative revenue per cohort by month-since.
  23. Q98Multi-fact daily spine mart (orders/returns/payments/views) for BI.
  24. Q99Streak & churn board: active streaks, dormancy, reactivation per customer.
  25. Q100Capstone: a parameterized daily time-series mart (complete spine, MAs, MoM/YoY, anomalies) as one query.

Production scenarios, optimisation

CONCEPTUAL

  1. Q1Architect a date-dimension + fact-spine layer for a warehouse (grain, keys, indexing).
  2. Q2Calendar dimension vs on-the-fly generate_series - tradeoffs at scale.
  3. Q3Incremental daily-metric materialization with idempotent backfill (Topic 25/26).
  4. Q4As-of / point-in-time joins against a spine - the generalized pattern.
  5. Q5Sessionization at scale: gap-based splitting, session keys, performance (Topic 19).
  6. Q6Gap-and-island internals: row_number-difference vs LAG-based boundary.
  7. Q7LOCF (last-observation-carried-forward) over sparse series via window.
  8. Q8Time-weighted averages (e.g. inventory) - integrating over a step function.
  9. Q9Multi-timezone reporting: store UTC, present per-region local - design.
  10. Q10DST-safe interval math and why raw epoch differences can mislead.
  11. Q11Rolling-window performance: RANGE frames, ordering, memory (Days 17/19).
  12. Q12ISO-year/week correctness and the off-by-one at year boundaries.
  13. Q13Fiscal calendars (4-4-5, April-start) - generating periods programmatically.
  14. Q14Cohort/retention triangle construction and storage shape (Topic 27).
  15. Q15Anomaly detection: percentile bands vs moving z-score (Topic 22).
  16. Q16Choosing spine grain + pre-aggregation tiers (day->week->month rollups).
  17. Q17Backfill vs streaming append for a metrics table - consistency concerns.
  18. Q18Handling late-arriving facts in a daily mart (idempotent upsert concept).
  19. Q19Heatmap normalization choices (per-row, per-col, global) and their meaning.
  20. Q20MV vs summary table vs live query for time-series (Topic 25) - trade-offs.
  21. Q21Indexing strategy for time-range dashboards: BRIN vs btree (Topic 20).
  22. Q22Why date_trunc in WHERE defeats indexes; the expression-index remedy.
  23. Q23Designing reusable parameterized spines (last-N-days, fiscal-YTD).
  24. Q24Reconciling rollups (daily sum vs monthly aggregate) - consistency checks.
  25. Q25SLA metric design over time: median/P95 windows and breach accounting.

TIME-SERIES & DISTRIBUTION ENGINES

  1. Q26Daily-revenue engine: 90-day spine, 7/28-day MA, MoM%, P95 anomaly flag, rank.
  2. Q27Cohort retention triangle (monthly cohorts x period 0..N) with %, complete spine.
  3. Q28Region growth engine: monthly revenue, MoM%, YoY%, rolling-12m, QoQ, rank - one query.
  4. Q29Sessionization engine: sessions per customer, count, median/P95 length, inter-event gaps.
  5. Q30Delivery-SLA time-series: median/P95 delivery days per region per month + breach % + trend.
  6. Q31Reactivation engine: dormancy (>90d) detection, reactivation per month, win-back %.
  7. Q32Seasonal-index engine per category (month / trailing-12m mean) as a forecast input.
  8. Q33Peak-load engine: busiest 15-min slots per weekday with median duration (Topic 22).
  9. Q34Anomaly engine: daily revenue beyond regional P95/P99 with context + streaks.
  10. Q35Fiscal engine: FYTD, QoQ, vs prior-FY same period, per region, fiscal-April.
  11. Q36As-of inventory engine: month-end latest snapshot per SKU + value + days-of-cover trend.
  12. Q37Streak & islands engine: per customer longest active streak + dormancy spells.
  13. Q38LOCF pricing engine: as-of monthly selling price per product, carry-forward gaps.
  14. Q39New/returning/reactivated revenue decomposition per month (three-way split).
  15. Q40Rolling-retention engine: 28-day active / prior 28-day base per region trend.
  16. Q41Heatmap engine: weekday x hour activity normalized, per device, with peak labels.
  17. Q42Time-to-deliver funnel engine: order->ship->deliver median/P95 per month per region.
  18. Q43ISO-week revenue engine 2024-2026 + YoY same-week + 4-week MA, boundary-correct.
  19. Q44Multi-fact daily mart: orders/returns/payments/views per day, gap-filled, one wide row.
  20. Q45Inter-order cadence engine: per customer median gap, distribution, cadence segments.
  21. Q46YoY decomposition: volume vs price vs mix contribution per month.
  22. Q47SLA-attainment engine per priority per month with target = prior-quarter P90 (Topic 22).
  23. Q48Customer-lifecycle engine: registration->first->latest spans + lifecycle stage per month.
  24. Q49Trailing-12-month LTV-by-cohort engine (cumulative revenue per period-since).
  25. Q50Reconciliation engine: daily-sum vs monthly-aggregate revenue per region (consistency).

CALENDAR/SPINE & AS-OF PIPELINES

  1. Q51Full 2018-2030 date dimension (calendar + fiscal + iso + flags) as a reusable spine.
  2. Q52Complete store x day revenue mart (cross-join spine) with zero-day diagnostics.
  3. Q53As-of join engine: for every month-end, latest inventory snapshot per SKU (Topic 22).
  4. Q54Cohort spine (cohort_month x period 0..23) joined to orders -> retention + LTV.
  5. Q55Business-day calendar (exclude weekends) -> business-days-to-deliver SLA.
  6. Q56LOCF carry-forward over a sparse daily metric per store (window LOCF).
  7. Q57Multi-grain rollup spine: day->week->month revenue with reconciliation (GROUPING SETS).
  8. Q58Sessionization spine: assign session ids by 30-min gap, then per-session metrics.
  9. Q59Fiscal-period spine (4-4-5 or April-start) + revenue per period + QoQ.
  10. Q60Heatmap spine weekday x hour, zero-filled, median duration per cell (Topic 22).
  11. Q61Reactivation spine: months-since-last-order per customer per month + state labels.
  12. Q62As-of price book: most-recent selling price per product at each month-end + drift.
  13. Q63Trailing-window spine: rolling 12-month revenue per region (RANGE frame).
  14. Q64Customer "age at order" cohorts via spine + registration join.
  15. Q65Snapshot-completeness checker: expected vs present daily snapshots per warehouse.
  16. Q66Unified activity spine: latest of orders/views/tickets/calls per customer per month.
  17. Q67Streak classifier via gap-and-island over a complete daily spine.
  18. Q68FYTD cumulative revenue per region (running sum on a fiscal spine).
  19. Q69Parameterized "last N days" spine factory + three pluggable KPIs.
  20. Q70As-of tier: customer's tier as-of each month-end (DISTINCT ON tier_updated_at, Topic 22).
  21. Q71Time-weighted average inventory per warehouse per month (step-function integral).
  22. Q72Spine-driven SLA: % delivered within 3 days per day + 30-day MA + breach streaks.
  23. Q73Late-arrival-tolerant daily mart (idempotent rebuild for a date range).
  24. Q74Heatmap normalization variants (row/col/global) from one spine dataset.
  25. Q75Reusable cohort-triangle builder parameterized by metric (orders/revenue/active).

PRODUCTION ANALYTICS SYSTEMS

  1. Q76CXO daily mart: 90-day spine, revenue, 7/28-day MA, MoM%, YoY%, P95 anomalies, rank.
  2. Q77Cohort retention + LTV dashboard: triangle %, cumulative revenue, latest cohort, decile.
  3. Q78Region growth command center: monthly revenue, MoM/YoY/QoQ, rolling-12m, rank, anomaly flag.
  4. Q79Delivery-SLA monitor: per region/month median/P95 delivery, breach %, streaks, target = prior-Q P90.
  5. Q80Sessionization + funnel: sessions, stage timings, median/P95 per stage, drop-off per hour.
  6. Q81Reactivation & churn system: dormancy detection, win-back per month, lifecycle stages.
  7. Q82Seasonal planning pack: per category seasonal index, trailing-12m, next-period hint.
  8. Q83Peak-load staffing report: busiest 15-min slots weekdayxhour + median handle time (calls).
  9. Q84Anomaly digest: daily revenue beyond regional P95/P99 with drill-down + recent streak.
  10. Q85Fiscal exec dashboard: FYTD, QoQ, prior-FY comparison per region, fiscal-April.
  11. Q86Inventory time-series board: month-end value, days-of-cover trend, stockout streaks.
  12. Q87Revenue decomposition system: new/returning/reactivated contribution per month per region.
  13. Q88Rolling-retention board: 28-day active/base per region + trend + alerts.
  14. Q89Delivery-funnel SLA: order->ship->deliver median/P95 per stage per month, end-to-end P95.
  15. Q90ISO-week YoY board: same-week revenue YoY per region, boundary-correct, ranked movers.
  16. Q91Heatmap product: weekdayxhour normalized activity per device for UX/ops.
  17. Q92Pricing drift monitor: as-of monthly price per product + corridor + drift alerts (Topic 22).
  18. Q93Customer lifecycle mart: per customer stage timeline + as-of tier + recency decile.
  19. Q94Reconciliation suite: daily vs monthly vs fiscal rollups consistency per region.
  20. Q95Multi-fact BI mart: per day per region orders/returns/payments/views, gap-filled, wide.
  21. Q96"Morning report" engine: yesterday vs trailing-28-day median per region (as-of) + flags.
  22. Q97Time-weighted inventory + turnover per warehouse per month.
  23. Q98Parameterized time-series mart factory (grain + metric + window pluggable).
  24. Q99End-to-end metrics pipeline: spine -> facts -> MAs -> MoM/YoY -> anomalies -> ranks, one query.
  25. Q100Capstone: the production daily/region time-series mart (complete spine, MAs, MoM/YoY/QoQ, P95 anomalies, SLA%, rank), noting where an MV (Topic 25) would replace live compute.