TheShiraverseSELECT * TheShiraverseCurriculumPracticeKahaniFAQGet StartedPlaygroundNukteTheShiraverse ↗
Practice › Topic 08

Aggregates and Grouping: 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

AGGREGATE / GROUP BY - CONCEPTUAL

  1. Q1Difference between COUNT(*) and COUNT(column_name).
  2. Q2What does COUNT(DISTINCT col) return?
  3. Q3Does AVG include NULLs in the denominator? Why does it matter?
  4. Q4What does SUM return on an empty result set - 0 or NULL?
  5. Q5Difference between WHERE and HAVING - give an example of each.
  6. Q6Why can't WHERE filter on COUNT(*)?
  7. Q7Rule: every non-aggregated SELECT column must appear in GROUP BY - why?
  8. Q8What is STRING_AGG and when is it useful?
  9. Q9Does STRING_AGG respect order? How do you control the order of concatenation?
  10. Q10Why is MIN/MAX valid on dates and strings, not just numbers?

SINGLE-NUMBER AGGREGATES (NO GROUP BY)

  1. Q11How many customers does RetailMart have?
  2. Q12How many orders has RetailMart processed in total?
  3. Q13How many delivered orders? (filter then COUNT).
  4. Q14How many customer reviews exist?
  5. Q15How many distinct cities do stores operate in?
  6. Q16How many distinct order_status values are in sales.orders?
  7. Q17How many distinct customer tiers exist?
  8. Q18How many products have a price above 1000?
  9. Q19How many tickets are currently 'Open'?
  10. Q20How many support tickets have NEVER been resolved?
  11. Q21What is the TOTAL revenue across all delivered orders? (SUM net_total).
  12. Q22What is the AVERAGE order net_total across all orders?
  13. Q23What is the AVERAGE salary across all employees?
  14. Q24What is the AVERAGE product price across the catalog?
  15. Q25What is the AVERAGE customer review rating?
  16. Q26What is the highest salary in the company?
  17. Q27What is the lowest salary in the company?
  18. Q28What is the most expensive product price?
  19. Q29What is the cheapest product price (above 0)?
  20. Q30What is the LATEST order date in sales.orders?
  21. Q31What is the EARLIEST order date in sales.orders?
  22. Q32What is the AVERAGE call_duration_seconds across all calls?
  23. Q33What is the MAX refund_amount in sales.returns?
  24. Q34What is the SUM of all marketing.campaigns budgets?
  25. Q35What is the AVERAGE finance.expenses amount?
  26. Q36What is the TOTAL finance.expenses amount for 2025?
  27. Q37What is the SUM of all payroll.pay_slips.net_salary? (Total salary payout.)
  28. Q38What is the MAX loyalty.members.points_balance?
  29. Q39What is the MIN loyalty.members.points_balance?
  30. Q40Get all 5 stats (count, sum, avg, min, max) of net_total on sales.orders in one row.

GROUP BY - SINGLE COLUMN

  1. Q41Count orders per order_status (sales.orders).
  2. Q42Count customers per tier (customers.customers).
  3. Q43Count tickets per priority.
  4. Q44Count tickets per status.
  5. Q45Count page_views per device_type.
  6. Q46Count application_logs per level.
  7. Q47Count api_requests per method.
  8. Q48Count api_requests per status_code (interesting distribution).
  9. Q49Count employees per role.
  10. Q50Count stores per city.
  11. Q51Count products per brand_id.
  12. Q52Count reviews per rating (1 through 5).
  13. Q53Count returns per reason.
  14. Q54Count ads_spend rows per platform.
  15. Q55SUM net_total per order_status.
  16. Q56SUM salary per role (employees).
  17. Q57AVG net_total per order_status.
  18. Q58AVG salary per dept_id (employees).
  19. Q59AVG rating per category in support tickets.
  20. Q60SUM budget per campaign month (DATE_TRUNC('month', start_date)).
  21. Q61Count orders per order_date month (DATE_TRUNC('month', order_date)).
  22. Q62Count customers per registration year (EXTRACT(YEAR FROM registration_date)).
  23. Q63Count calls per call_reason.
  24. Q64SUM call_duration_seconds per agent_id (which agent talks the most?).
  25. Q65Count attendance entries per employee_id.
  26. Q66Count tickets per customer_id.
  27. Q67SUM refund_amount per return reason.
  28. Q68SUM amount per exp_cat_id (finance.expenses).
  29. Q69MAX salary per dept_id.
  30. Q70MIN price per brand_id.

HAVING + STRING_AGG

  1. Q71List order statuses with more than 1,000 orders (GROUP BY ... HAVING COUNT > 1000).
  2. Q72List cities with more than 5 stores (HAVING COUNT > 5).
  3. Q73List tiers with more than 5,000 customers.
  4. Q74List brands with more than 100 products.
  5. Q75List employee roles with more than 50 people.
  6. Q76List rating values where there are 1000+ reviews.
  7. Q77List call_reasons with more than 500 calls.
  8. Q78List page_view device_types with more than 100,000 views.
  9. Q79List api_request methods with more than 1,000 hits.
  10. Q80List ticket priorities where >= 1000 tickets exist.
  11. Q81Per role, only roles whose AVG salary > 50000.
  12. Q82Per brand_id, only brands whose AVG product price > 1000.
  13. Q83Per order_status, only statuses whose SUM net_total > 1,000,000.
  14. Q84Per registration year, only years with > 5,000 new customers.
  15. Q85Per month, only months with more than 5,000 orders.
  16. Q86Per dept_id, only departments where SUM salary > 10,000,000.
  17. Q87Per campaign month, only months with total budget > 1,000,000.
  18. Q88Per platform (ads_spend), only platforms with SUM amount > 1,000,000.
  19. Q89Per category (tickets), only categories with more than 500 tickets.
  20. Q90Per exp_cat_id, only categories with sum amount > 5,000,000.
  21. Q91STRING_AGG: comma-separated list of distinct order_status from sales.orders.
  22. Q92STRING_AGG: comma-separated list of distinct cities from stores.stores.
  23. Q93STRING_AGG: comma-separated list of distinct tiers from customers.customers, alphabetised.
  24. Q94STRING_AGG: per role, list ALL employee first_names joined by ', '.
  25. Q95STRING_AGG DISTINCT: per call_reason, distinct status values.
  26. Q96STRING_AGG: per priority, all ticket subjects (LIMIT to a small group).
  27. Q97STRING_AGG: per device_type, distinct OS values (web_events.page_views).
  28. Q98STRING_AGG: per dept_id, list of employee first names ordered alphabetically.
  29. Q99STRING_AGG: per brand_id, list of product names (might be long - verify on a small sample).
  30. Q100STRING_AGG with ORDER BY inside: per ticket category, subjects ordered by created_date.

Combined ideas, multi-step thinking

AGGREGATE DEEPER - CONCEPTUAL

  1. Q1Difference between COUNT(*), COUNT(col), COUNT(DISTINCT col) - give a row-set example for each.
  2. Q2Why does SUM(CASE WHEN x = 'A' THEN amount END) work for filtered sums while WHERE doesn't (in the same statement)?
  3. Q3Compare AVG(col) vs AVG(COALESCE(col, 0)) - when do they give different answers?
  4. Q4What's the difference between SUM(amount) and SUM(DISTINCT amount)?
  5. Q5Explain what FILTER (WHERE ...) does - and how it compares to CASE inside aggregates.
  6. Q6What happens to SUM/AVG/COUNT when the WHERE eliminates ALL rows - is the result NULL or 0?
  7. Q7Why must non-aggregated columns appear in GROUP BY? Show what happens if you forget.
  8. Q8Can you GROUP BY an expression (DATE_TRUNC(...))? Show example.
  9. Q9Compare GROUP BY col vs GROUP BY 1 - when is each safer?
  10. Q10Explain HAVING vs WHERE again - but with a CASE-driven aggregate example.
  11. Q11What does GROUP BY ROLLUP(a, b) produce that GROUP BY a, b does not?
  12. Q12Compare GROUP BY CUBE vs GROUP BY ROLLUP - which produces MORE subtotal rows?
  13. Q13What is GROUPING SETS - and when do you need it over CUBE/ROLLUP?
  14. Q14Explain the GROUPING() function - what does GROUPING(col) return for subtotal rows?
  15. Q15Why is SELECT col, COUNT(*) FROM t without GROUP BY an error if col isn't aggregated?
  16. Q16Can ORDER BY reference an aggregate alias? Compare with HAVING.
  17. Q17What's the difference between PERCENTILE_CONT and PERCENTILE_DISC? (Brief - full coverage Topic 22.)
  18. Q18Why is GROUP BY on a TIMESTAMP column rarely useful - and what's the standard fix?
  19. Q19What does ARRAY_AGG do - give a use case.
  20. Q20Difference between STRING_AGG and ARRAY_AGG.
  21. Q21What does JSON_AGG produce - and when is it useful for APIs?
  22. Q22Why does COUNT(*) often outperform COUNT(col) in PostgreSQL?
  23. Q23Explain the "1 + GROUP BY trick" (SELECT 1 FROM t GROUP BY ...). When is this used?
  24. Q24What is BIT_AND / BIT_OR / BOOL_AND / BOOL_OR? Give a use case for BOOL_OR.
  25. Q25Why is GROUP BY + ORDER BY + LIMIT often a complete reporting unit?

CONDITIONAL AGGREGATION

  1. Q26Per order_status: COUNT(*) AND SUM(net_total) in one row using GROUP BY.
  2. Q27Single-row summary: COUNT(*) for each status using SUM(CASE WHEN status='X' THEN 1 END).
  3. Q28Total delivered revenue + total cancelled count in ONE row using conditional SUM/COUNT.
  4. Q29Per customer tier, count customers AND average tenure in years.
  5. Q30Per ticket priority, count tickets + average resolution time in hours.
  6. Q31Per device_type, count page_views + count distinct customer_id (excludes NULL anonymous).
  7. Q32Per role, count employees + average salary + max salary + min salary.
  8. Q33Per call_reason, count + average call_duration + count of long calls (> 300s).
  9. Q34Per brand_id, count products + count premium products (price > 1000) + average price.
  10. Q35Per category (support tickets), count + percent resolved (use CASE + COUNT).
  11. Q36Per registration year, count new customers + count of Gold/Platinum.
  12. Q37Per region, count stores + count distinct cities.
  13. Q38Single row: count distinct customer_ids in sales.orders AND in customers.reviews AND in support.tickets.
  14. Q39Single row: count of orders by status using FILTER (WHERE ...) - 4 columns: delivered_count, cancelled_count, pending_count, total_count.
  15. Q40Per platform (ads_spend), SUM amount + COUNT rows + AVG amount per spend record.
  16. Q41Per ad campaign month, SUM ad spend + COUNT campaigns active.
  17. Q42Per exp_cat_id, SUM amount + COUNT + AVG + STDDEV (variance check on expenses).
  18. Q43Per pay_slip salary_month, SUM gross_salary + SUM net_salary + SUM income_tax.
  19. Q44Per work_order line_id, SUM quantity_produced + SUM rejected_quantity + rejection rate %.
  20. Q45Per warehouse, SUM quantity_on_hand from inventory_snapshots (latest snapshot only).
  21. Q46Per supplier_id, COUNT products + AVG price + MAX price + MIN price.
  22. Q47Per loyalty tier, COUNT members + AVG points_balance + MAX points_balance.
  23. Q48Per support category, percentage of Open tickets (FILTERED count / total).
  24. Q49Per page_view URL, count distinct customer_id + count distinct session_id.
  25. Q50Per device_type + os combination, COUNT page_views.

MULTI-COLUMN GROUP BY + DATE BUCKETS

  1. Q51Per order_status AND month, COUNT orders + SUM net_total.
  2. Q52Per customer tier AND registration year, COUNT customers.
  3. Q53Per ticket priority AND status, COUNT tickets.
  4. Q54Per device_type AND month, COUNT page_views.
  5. Q55Per platform AND month, SUM ads_spend amount.
  6. Q56Per role AND store_id, COUNT employees + AVG salary.
  7. Q57Per brand_id AND is_premium (price > 1000), COUNT products.
  8. Q58Per call_reason AND status, COUNT calls + SUM duration.
  9. Q59Per campaign year AND quarter, SUM budget.
  10. Q60Per shipment courier_name AND month, COUNT shipments.
  11. Q61Per attendance year AND month, COUNT records + SUM (check_out - check_in) duration.
  12. Q62Per orders status AND store, COUNT + SUM net_total.
  13. Q63Per customer city AND tier, COUNT customers.
  14. Q64Per refund reason AND month, COUNT returns + SUM refund_amount.
  15. Q65Per priority AND week (DATE_TRUNC('week', created_date)), COUNT tickets.
  16. Q66Per HTTP method AND status_code class (2xx/3xx/etc.), COUNT api_requests.
  17. Q67Per app log level AND service_name, COUNT logs.
  18. Q68Per region AND week, COUNT stores opened.
  19. Q69Per fiscal_quarter (custom CASE) + brand_id, COUNT products.
  20. Q70Per shift (CASE on EXTRACT(HOUR)) + day_of_week, COUNT calls.
  21. Q71Per supplier_id + warehouse_id, SUM quantity from supply_chain.shipments.
  22. Q72Per work_orders.line_id + status, COUNT + SUM quantity_produced.
  23. Q73Per exp_cat_id + month, SUM amount.
  24. Q74Per loyalty.tier_id + month-of-join, COUNT members.
  25. Q75Per pay_slip salary_year + salary_month, SUM gross_salary across all employees.

HAVING / FILTER / GROUPING SETS

  1. Q76Show order_statuses with > 1000 orders AND SUM(net_total) > 5,000,000 (multi-condition HAVING).
  2. Q77Show customers (GROUPed by customer_id from orders) who placed > 10 orders.
  3. Q78Show product_ids in order_items with SUM(quantity) > 100.
  4. Q79Show employees with SUM(salary across pay_slips) > 1,000,000 (HAVING on aggregated payroll).
  5. Q80Show platforms in ads_spend with AVG(amount) > 5000 and COUNT > 10.
  6. Q81Show brands with > 50 products AND AVG(price) BETWEEN 1000 AND 10000.
  7. Q82Show stores in cities with COUNT >= 3 stores (cities to consolidate).
  8. Q83Show tier_id from loyalty.members with > 1000 members AND AVG(points_balance) > 500.
  9. Q84Show categories of tickets with COUNT > 500 AND resolution_rate < 50% (HAVING using CASE-COUNT ratio).
  10. Q85Show months with > 10000 orders AND SUM(net_total) > 50,000,000.
  11. Q86Using FILTER: count orders per status in a single row (Delivered, Cancelled, Pending, etc.)
  12. Q87Using FILTER: count distinct customers per tier in one row of 4 columns.
  13. Q88Using FILTER: SUM revenue per region in one row.
  14. Q89Using ROLLUP: per order_status and month, SUM + subtotals per status + grand total.
  15. Q90Using CUBE: per device_type AND os, COUNT page_views + all subtotals + grand total.
  16. Q91Using GROUPING SETS: separately group by (tier) AND (registration_year) - two reports in one query.
  17. Q92ROLLUP on (dept_id, role) for employee counts.
  18. Q93Use GROUPING() to identify subtotal rows in a ROLLUP output.
  19. Q94Combine FILTER + GROUP BY: per region, count delivered orders AND cancelled orders.
  20. Q95Per platform, SUM amount FILTER WHERE spend_date >= '2025-01-01'.
  21. Q96Per product brand, count of 5-star reviews (FILTER WHERE rating = 5).
  22. Q97Use STRING_AGG with GROUP BY: per category, comma-separated list of ticket subjects (latest 5).
  23. Q98Per region, top-3 stores by SUM(net_total) - needs a window function (peek at Topic 16).
  24. Q99Per customer tier, MAX, MIN, AVG of points_balance from loyalty.members.
  25. Q100Per ticket category, percent of tickets handled by 'High' priority + percent by 'Critical' - using FILTER counts divided by total.

Interview grade, edge cases

AGGREGATES - CONCEPTUAL

  1. Q1Compare PERCENTILE_CONT vs PERCENTILE_DISC - which interpolates?
  2. Q2Why does median require WITHIN GROUP (ORDER BY ...)?
  3. Q3What is an "ordered-set aggregate" in PostgreSQL?
  4. Q4Explain how PERCENTILE_CONT(0.5) computes median for even-count groups (interpolates).
  5. Q5Compare MODE() WITHIN GROUP vs custom CASE-COUNT logic.
  6. Q6What does ROLLUP(a, b, c) produce - listed subtotals.
  7. Q7What does CUBE(a, b, c) produce - all 2^3 subtotal combinations.
  8. Q8GROUPING SETS - when is it more flexible than CUBE/ROLLUP?
  9. Q9GROUPING(col) function - how to read 1/0 in subtotal rows.
  10. Q10ARRAY_AGG(col ORDER BY ...) - what's stored vs unordered?
  11. Q11STRING_AGG(col, ',' ORDER BY col) - when ORDER BY is non-deterministic.
  12. Q12JSON_AGG vs JSONB_AGG - when each.
  13. Q13JSONB_OBJECT_AGG - build a key->value map from rows.
  14. Q14ARRAY_AGG(DISTINCT col) - when DISTINCT matters.
  15. Q15SUM(DISTINCT col) - uncommon but valid case.
  16. Q16Explain FILTER WHERE - when this is cleaner than CASE WHEN ... THEN ... END.
  17. Q17Multi-FILTER in one aggregate row: COUNT(*) FILTER WHERE A, COUNT(*) FILTER WHERE B.
  18. Q18BIT_AND / BIT_OR - when does bit-aggregation matter (flags).
  19. Q19BOOL_AND / BOOL_OR - used for "all-or-any" group checks.
  20. Q20HAVING with subquery - can it reference outer GROUP BY columns?
  21. Q21Why does GROUP BY GROUPING SETS((a), (b)) produce 2 reports in one query?
  22. Q22Explain DISTINCT ON inside aggregation contexts.
  23. Q23What does AVG(NULL, NULL, 5) return in Postgres? (Skips NULLs.)
  24. Q24Why does SUM() on an empty set return NULL but COUNT() return 0?
  25. Q25ORDER BY inside aggregate (ARRAY_AGG col ORDER BY ...) - execution model.

PERCENTILES & STATISTICAL AGGREGATES

  1. Q26Median order net_total.
  2. Q27P90 order net_total.
  3. Q28P99 order net_total.
  4. Q29Median, P90, P99 in one row.
  5. Q30Median resolution_time per ticket priority.
  6. Q31Median time-to-deliver per courier.
  7. Q32P95 call_duration per call_reason.
  8. Q33P99 page_view dwell time per device_type.
  9. Q34Median product price per brand.
  10. Q35Median pay_slip gross_salary per dept.
  11. Q36STDDEV salary per dept.
  12. Q37STDDEV ad spend per platform.
  13. Q38VARIANCE order net_total per status.
  14. Q39MIN, MAX, AVG, MEDIAN per store.
  15. Q40Quartiles (P25, P50, P75) of order_total per region.
  16. Q41MODE() WITHIN GROUP - most-frequent order_status.
  17. Q42MODE() WITHIN GROUP - most-frequent city per tier.
  18. Q43PERCENT_RANK preview: identify orders in top 10% by net_total.
  19. Q44CUME_DIST preview: cumulative distribution of order_total.
  20. Q45Range (MAX - MIN) of order_total per region.
  21. Q46Coefficient of variation = STDDEV / AVG.
  22. Q47Skewness check: AVG vs MEDIAN per category (proxy: comparing means).
  23. Q48P95 vs MAX delivery_days - outlier detection.
  24. Q49Median revenue per fiscal quarter.
  25. Q50PERCENTILE_DISC vs PERCENTILE_CONT comparison on the same set.

GROUPING SETS / CUBE / ROLLUP

  1. Q51ROLLUP(region, status): per-region+status counts + per-region subtotal + grand total.
  2. Q52CUBE(device_type, os): all 2^2 subtotal combinations.
  3. Q53GROUPING SETS((priority), (status)): two reports in one query.
  4. Q54ROLLUP(year, month) for monthly revenue + yearly subtotals + grand total.
  5. Q55CUBE(brand_id, category_id): full cross-tabulation of brand x category.
  6. Q56GROUPING SETS((cust_tier, region), (cust_tier), ()): hierarchical revenue report.
  7. Q57ROLLUP(dept, role) for employee counts + dept subtotals + grand total.
  8. Q58Use GROUPING(col) to label subtotal rows clearly.
  9. Q59Filter only subtotal rows: WHERE GROUPING(col) = 1.
  10. Q60ROLLUP(region, store): regional + store-level summaries.
  11. Q61CUBE(payment_method, status): all combinations of payment+status counts.
  12. Q62GROUPING SETS for marketing dashboard: campaign-level + platform-level + grand total.
  13. Q63ROLLUP(supplier, product): supplier-level + product-level inventory totals.
  14. Q64ROLLUP(year, quarter) - fiscal report.
  15. Q65CUBE(brand, region, category): 3-dim subtotals.
  16. Q66GROUPING SETS with empty (): include grand-total row.
  17. Q67ORDER BY with GROUPING() puts subtotals at the bottom.
  18. Q68ROLLUP-based "P&L hierarchy" report: cat -> subcat -> grand.
  19. Q69CUBE-based "matrix" report: row=region, col=channel.
  20. Q70GROUPING SETS to merge "by tier" and "by year" in one result.
  21. Q71Replace 3 UNION ALL queries with 1 GROUPING SETS query.
  22. Q72Show GROUPING IDs (binary) for each row in a CUBE.
  23. Q73Top-N within each grouping level (combine ROLLUP + windows preview).
  24. Q74Conditional subtotal label using CASE + GROUPING().
  25. Q75Per region per quarter, count orders + subtotal per region + grand total.

ARRAY/STRING/JSON_AGG + custom aggregates

  1. Q76Per category, ARRAY_AGG(product_name) - list every product.
  2. Q77Per region, ARRAY_AGG(store_name ORDER BY store_name).
  3. Q78Per customer, ARRAY_AGG(order_id ORDER BY order_date DESC).
  4. Q79Per dept, STRING_AGG(employee_name, ', ' ORDER BY hire_date).
  5. Q80Per priority, STRING_AGG(ticket_subject, ' | ' ORDER BY created_date DESC) LIMIT-style.
  6. Q81Per courier, JSON_AGG(JSON_BUILD_OBJECT('shipment_id', s, 'date', d)).
  7. Q82Per campaign, JSONB_OBJECT_AGG(platform, total_spend).
  8. Q83Per customer, JSON_AGG(orders) returning a JSON array.
  9. Q84Per category, ARRAY_AGG(DISTINCT brand_name).
  10. Q85Per agent, JSONB_AGG(JSON_BUILD_OBJECT('ticket', id, 'status', status)).
  11. Q86Per store, ARRAY_AGG(role) FILTER WHERE active.
  12. Q87Per session, ARRAY_AGG(url ORDER BY view_time) - clickstream.
  13. Q88Per warehouse, ARRAY_AGG(product_id) FILTER WHERE quantity_on_hand < 10.
  14. Q89Per call_reason, STRING_AGG(transcript_summary, ' --- ' ORDER BY call_date DESC).
  15. Q90Per tier, ARRAY_AGG(member_email).
  16. Q91BIT_OR(flags) - combine bit flags per group.
  17. Q92BOOL_OR(is_premium) - does this group have any premium customer.
  18. Q93BOOL_AND(is_compliant) - group fully compliant flag.
  19. Q94Custom percent: SUM(CASE WHEN x THEN 1 ELSE 0 END) * 100.0 / COUNT(*) per group.
  20. Q95Custom running total via CASE-CUM inside ARRAY_AGG (preview).
  21. Q96Build a hash of group via md5(STRING_AGG(col, ',' ORDER BY col)).
  22. Q97Build a "set diff" using two ARRAY_AGGs and array operators.
  23. Q98ARRAY_AGG combined with WITH ORDINALITY: preserve original order.
  24. Q99JSON_AGG to build a nested structure for an API response.
  25. Q100Per customer, JSONB_BUILD_OBJECT('orders', JSON_AGG(...), 'reviews', JSON_AGG(...)) - full customer card.

Production scenarios, optimisation

OLAP CUBES

  1. Q1CUBE(region, month, status) - full 3D.
  2. Q2ROLLUP(year, quarter, month) - hierarchical.
  3. Q3GROUPING SETS for parallel cohort views.
  4. Q4Per region x month x status, revenue + subtotals.
  5. Q5Per brand x category x region, revenue.
  6. Q6Per tier x age_band x city, count.
  7. Q7Per priority x dept x month, ticket count.
  8. Q8Per supplier x warehouse x month, shipment count.
  9. Q9Per platform x campaign x month, ad spend.
  10. Q10Per device x os x browser, page view count.
  11. Q11Per category x brand x supplier x region, sales.
  12. Q12Use GROUPING() to label rows.
  13. Q13Filter only subtotal rows.
  14. Q14Filter only grand total.
  15. Q15Cubes with multiple measures (SUM + AVG + COUNT).
  16. Q16Cubes with FILTER per measure.
  17. Q17Per region x month x status as a cross-tab.
  18. Q18Year-over-year cube.
  19. Q19Quarter-over-quarter cube.
  20. Q20Day-of-week x hour-of-day cube.
  21. Q21Customer cohort x month x tier cube.
  22. Q22Ad attribution cube: campaign x source x medium x geo.
  23. Q23Fiscal cube: fiscal year x quarter x dept.
  24. Q24Combine ROLLUP with FILTER (per-status totals).
  25. Q25Build executive cube: 6 dimensions x 4 measures.

DISTRIBUTIONS & STATISTICS

  1. Q26Distribution of net_total: 20-bucket histogram.
  2. Q27Distribution of order_date: monthly bucket.
  3. Q28Distribution of ticket priority.
  4. Q29Distribution of page_views per session.
  5. Q30Customer LTV distribution.
  6. Q31Time-to-deliver distribution.
  7. Q32Time-to-resolve distribution.
  8. Q33Refund-rate distribution per product.
  9. Q34Review-length distribution.
  10. Q35Median + MAD (median absolute deviation).
  11. Q36Mean / median / mode together.
  12. Q37Skewness: (mean - median) / stddev.
  13. Q38Z-score of each order vs population.
  14. Q39Outlier detection: z > 3.
  15. Q40IQR outlier detection (1.5 x IQR).
  16. Q41Pearson correlation (price vs rating).
  17. Q42Spearman correlation (preview).
  18. Q43Covariance between two metrics.
  19. Q44Linear regression slope (REGR_SLOPE).
  20. Q45R^2 (REGR_R2).
  21. Q46Moving average (preview window).
  22. Q47Exponentially weighted moving average.
  23. Q48Anomaly detection via 7-day SMA vs current.
  24. Q49Seasonality detection (week-over-week diff).
  25. Q50P-value for difference of means (manual computation).

PIVOTS & UNPIVOTS

  1. Q51Pivot orders by month: rows = year, columns = month.
  2. Q52Pivot tickets by status: rows = priority, columns = status.
  3. Q53Pivot revenue by region x tier.
  4. Q54Pivot reviews by rating: rows = brand, columns = rating.
  5. Q55Pivot inventory by warehouse x product.
  6. Q56Pivot pay_slips by year x dept.
  7. Q57Pivot calls by reason x shift.
  8. Q58Pivot campaigns by platform x month.
  9. Q59Unpivot order_items metrics.
  10. Q60Unpivot per-customer scoring.
  11. Q61Crosstab with N dynamic columns (tablefunc extension).
  12. Q62Pivot using FILTER for each column.
  13. Q63Pivot using CASE for each column.
  14. Q64Pivot using ARRAY_AGG.
  15. Q65Pivot using jsonb_object_agg.
  16. Q66Pivot with percentage of total per row.
  17. Q67Pivot with running total.
  18. Q68Pivot with rank within row.
  19. Q69Reverse pivot via UNION ALL.
  20. Q70Reverse pivot via jsonb_each.
  21. Q71Stacked column report.
  22. Q72Sparse vs dense pivot.
  23. Q73Pivot with nulls treated as 0.
  24. Q74Multi-measure pivot.
  25. Q75Pivot with comparison vs prior period.

FUNNELS & ANOMALIES

  1. Q76Funnel: visit -> signup -> first order.
  2. Q77Funnel: signup -> activation -> retention -> revenue.
  3. Q78Funnel: campaign click -> cart -> checkout -> paid.
  4. Q79Funnel conversion %.
  5. Q80Funnel drop-off per step.
  6. Q81Time-between-step funnel.
  7. Q82Cohort funnel by signup month.
  8. Q83Multi-touch attribution.
  9. Q84First-touch attribution.
  10. Q85Last-touch attribution.
  11. Q86Linear-touch attribution.
  12. Q87Time-decay attribution.
  13. Q88Anomaly: revenue today vs 30-day avg.
  14. Q89Anomaly: ticket count today vs 30-day avg.
  15. Q90Anomaly: error log spike.
  16. Q91Anomaly: SLA breach rate.
  17. Q92Anomaly: per-product return rate.
  18. Q93Anomaly: customer behavior drift.
  19. Q94Outlier orders.
  20. Q95Outlier returns.
  21. Q96Detect bot traffic.
  22. Q97Detect data quality issues (sudden NULLs).
  23. Q98Detect schema drift.
  24. Q99Detect "stuck" inventory (not moving).
  25. Q100Build a "RetailMart anomaly summary" - 20 detectors.