TheShiraverseSELECT * TheShiraverseCurriculumPracticeKahaniFAQGet StartedPlaygroundNukteTheShiraverse ↗
Practice › Topic 21

Pivoting, Unpivoting and FILTER: 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 "pivoting" mean (rows -> columns)?
  2. Q2What does the FILTER (WHERE ...) clause do on an aggregate?
  3. Q3Rewrite SUM(CASE WHEN c THEN x END) as SUM(x) FILTER (WHERE c).
  4. Q4Why is FILTER cleaner/clearer than CASE-inside-aggregate?
  5. Q5What does "conditional aggregation" mean?
  6. Q6Why must pivot columns be a known/fixed set in plain SQL?
  7. Q7How is dynamic pivot done (crosstab/tablefunc) - and why it needs an extension?
  8. Q8What does "unpivoting" mean (columns -> rows)?
  9. Q9How does UNION ALL unpivot a wide table into long format?
  10. Q10When should you pivot in SQL vs leave it to the BI tool?
  11. Q11Why does COUNT(*) FILTER (WHERE c) count only matching rows?
  12. Q12What does a pivot's GROUP BY key become (the row label)?
  13. Q13How do you get a percent-of-row-total in a pivot?
  14. Q14Why might pivoted NULLs need COALESCE(..., 0)?
  15. Q15Can you mix FILTER with GROUP BY? (yes - show the shape)
  16. Q16Difference between SUM(x) FILTER (WHERE c) and SUM(x) WHERE c.
  17. Q17What is unnest() and how can it help unpivot arrays?
  18. Q18Why is a "category x month" matrix a classic pivot?
  19. Q19How many output columns does a pivot of N categories produce?
  20. Q20What's the risk of pivoting a high-cardinality column?
  21. Q21How do you pivot a count vs a sum?
  22. Q22Why is FILTER evaluated per aggregate (independent conditions)?
  23. Q23How do you label unpivoted rows with the source column name?
  24. Q24When is GROUPING SETS/ROLLUP a better fit than a manual pivot?
  25. Q25Name two analyst reports that are pivots and two that are unpivots.

FILTER CLAUSE

  1. Q26Count of Delivered vs Cancelled orders using two FILTERed COUNTs.
  2. Q27Revenue from Delivered orders only via SUM(net_total) FILTER.
  3. Q28Per store: count of each order_status in separate FILTERed columns.
  4. Q29Per customer: count of orders and count of Returned orders (FILTER).
  5. Q30Per product: count of 5-star vs 1-star reviews (FILTER).
  6. Q31Per agent: open vs resolved ticket counts (FILTER).
  7. Q32Per region: total revenue and revenue from Gold/Platinum customers (FILTER).
  8. Q33Per month: revenue and order count (FILTER by date range).
  9. Q34Count of customers with vs without a phone (FILTER IS NULL).
  10. Q35Per brand: count of products above vs below Rs5000 (FILTER).
  11. Q36Per store: revenue this year vs last year (two FILTERs).
  12. Q37Per courier: on-time vs late delivery counts (FILTER on day diff).
  13. Q38Per department: count of high earners (salary>50000) via FILTER.
  14. Q39Per platform: spend in Q1 vs Q2 (FILTER by date).
  15. Q40Per customer: count of orders in each tier of net_total (3 FILTERs).
  16. Q41% of orders Delivered = COUNT FILTER / COUNT(*).
  17. Q42Per category: revenue and units (FILTER not needed) plus returned units (FILTER).
  18. Q43Per region: count of stores opened before vs after 2023 (FILTER).
  19. Q44Per product: avg rating and count of negative reviews (FILTER).
  20. Q45Per store: weekday vs weekend revenue (FILTER on DOW).
  21. Q46Count of payments by each payment_mode using FILTER.
  22. Q47Per agent: calls under 60s vs over 300s (FILTER).
  23. Q48Per customer: spend on Delivered vs spend on Returned (FILTER).
  24. Q49Per month: new customers (FILTER by registration month).
  25. Q50Combine FILTER columns into one summary row per store.

CASE-BASED PIVOT

  1. Q51Revenue by month with one column per payment_mode (FILTER pivot).
  2. Q52Order count by region with one column per status.
  3. Q53Per brand: count of products in price bands (value/mid/premium) as columns.
  4. Q54Per store: revenue per quarter (Q1..Q4 columns).
  5. Q55Per category: units sold per month (Jan..Dec columns) for one year.
  6. Q56Per agent: ticket counts per priority (Critical/High/Medium/Low columns).
  7. Q57Per region: customer counts per tier (Bronze..Platinum columns).
  8. Q58Per product: review counts per rating (1..5 columns).
  9. Q59Per courier: shipment counts per status as columns.
  10. Q60Per department: headcount per role as columns.
  11. Q61Per store: order counts per weekday (Mon..Sun columns).
  12. Q62Per platform: spend per quarter as columns.
  13. Q63Revenue per region x payment_mode matrix (region rows, mode columns).
  14. Q64Per month: counts of each order_status as columns.
  15. Q65Per brand: revenue per tier-of-customer as columns.
  16. Q66Per warehouse: stock per category as columns (via product->brand).
  17. Q67Per customer cohort (reg year): order counts per following year.
  18. Q68Per store: this-year vs last-year revenue as two columns.
  19. Q69Per category: returned vs delivered units as columns.
  20. Q70Pivot with COALESCE to show 0 instead of NULL in empty cells.
  21. Q71Per region: avg order value per quarter as columns.
  22. Q72Per product: units in each season (spring/summer/...) as columns.
  23. Q73Per agent: avg resolution hours per priority as columns.
  24. Q74Per store: count of orders per net_total band as columns + row total.
  25. Q75Build a clean "status x month" pivot for the ops team.

UNPIVOT & PERCENT-OF-ROW

  1. Q76Unpivot a 4-quarter KPI (one row, 4 cols) into 4 rows via UNION ALL.
  2. Q77Unpivot revenue_summary-style columns into (metric, value) rows.
  3. Q78Unpivot pay_slip components (basic, hra, pf, tax...) into long format.
  4. Q79Unpivot a region's Q1..Q4 revenue into (quarter, revenue).
  5. Q80Unpivot email_clicks (sent, opened, clicked) into (stage, count).
  6. Q81Unpivot a product's price and cost_price into (kind, amount).
  7. Q82Unpivot order totals (gross, discount, net) into rows.
  8. Q83Pivot then add a percent-of-row-total column per status.
  9. Q84Per region status-pivot with each status as % of the region's orders.
  10. Q85Pivot revenue by payment_mode with a row total and per-mode %.
  11. Q86Unpivot a wide "monthly_kpis" mock (use revenue_summary) to long.
  12. Q87Unpivot using unnest over an ARRAY of values.
  13. Q88Unpivot shipment date columns (shipped, delivered) into events.
  14. Q89Percent-of-row pivot: brand price-band counts as % of brand total.
  15. Q90Unpivot a customer's contact fields (email, phone) into (channel, value).
  16. Q91Pivot orders-by-week-by-status, then % per status within each week.
  17. Q92Unpivot tax_brackets columns into long format.
  18. Q93Percent-of-column-total in a region x status pivot.
  19. Q94Unpivot a wide quarterly sales row into a tidy long table.
  20. Q95Pivot tier counts per region and show each tier's % of region.
  21. Q96Unpivot then re-pivot (round-trip) to verify equivalence.
  22. Q97Build a long-format metric table from several aggregate columns.
  23. Q98Pivot with both row totals and column totals (manual margins).
  24. Q99Note when to use crosstab() (tablefunc, practice) for dynamic columns.
  25. Q100Build a category x quarter revenue matrix with row & column totals.

Combined ideas, multi-step thinking

CONCEPTUAL

  1. Q1Why does a two-dimensional pivot need GROUP BY on the row key only?
  2. Q2How to add a row total to a pivot (extra SUM without FILTER).
  3. Q3How to add a grand total / column total (UNION or GROUPING SETS).
  4. Q4Percent-of-row: divide each cell by the row's total.
  5. Q5Percent-of-column: divide each cell by a window/subquery column total.
  6. Q6Why FILTER conditions are independent per aggregate.
  7. Q7How to pivot a count and a sum in the same query.
  8. Q8When to COALESCE pivot cells to 0.
  9. Q9Unpivot with UNION ALL: structure and the label column.
  10. Q10Unpivot with unnest over parallel ARRAYs of names/values.
  11. Q11Why dynamic pivot (unknown categories) needs crosstab or app code.
  12. Q12GROUPING SETS vs manual pivot for subtotals.
  13. Q13Pivot then compute a derived ratio column (e.g. return rate).
  14. Q14Why high-cardinality pivots are usually a BI-tool job.
  15. Q15Round-trip: pivot then unpivot should recover the long form.
  16. Q16Conditional aggregation with AVG FILTER vs SUM/COUNT FILTER.
  17. Q17How to pivot booleans (bool_or/bool_and) into yes/no columns.
  18. Q18Pivoting dates into period buckets (month/quarter) columns.
  19. Q19Why pivot output column names must be static identifiers.
  20. Q20How to keep a pivot stable when a category has zero rows.
  21. Q21Difference between unpivot via UNION ALL vs via a VALUES + JOIN.
  22. Q22Percent-of-grand-total in a 2-D matrix.
  23. Q23Adding both row % and column % to the same matrix.
  24. Q24When jsonb_object_agg gives a "pivot-like" dynamic result (concept; JSON is Topic 24).
  25. Q25A checklist for building a correct pivot report.

MULTI-DIMENSIONAL PIVOTS

  1. Q26Region (rows) x order_status (columns) order-count matrix.
  2. Q27Month (rows) x payment_mode (columns) revenue matrix.
  3. Q28Brand (rows) x price-band (columns) product-count matrix.
  4. Q29Store (rows) x quarter (columns) revenue matrix.
  5. Q30Category (rows) x month (columns) units matrix (one year).
  6. Q31Agent (rows) x priority (columns) ticket-count matrix.
  7. Q32Region (rows) x tier (columns) customer-count matrix.
  8. Q33Product (rows) x rating (columns) review-count matrix.
  9. Q34Courier (rows) x on-time/late (columns) shipment matrix.
  10. Q35Department (rows) x role (columns) headcount matrix.
  11. Q36Store (rows) x weekday (columns) revenue matrix.
  12. Q37Region (rows) x payment_mode (columns) revenue + row total.
  13. Q38Brand (rows) x customer-tier (columns) revenue matrix.
  14. Q39Category (rows) x returned/delivered (columns) units matrix.
  15. Q40Month (rows) x status (columns) order-count + grand total row.
  16. Q41Warehouse (rows) x category (columns) stock matrix.
  17. Q42Cohort-year (rows) x following-year (columns) order-count matrix.
  18. Q43Platform (rows) x quarter (columns) spend matrix.
  19. Q44Region (rows) x season (columns) revenue matrix.
  20. Q45Store (rows) x net_total-band (columns) order-count matrix.
  21. Q46City (rows) x tier (columns) customer-count matrix (via addresses).
  22. Q47Product (rows) x month (columns) revenue with a row total.
  23. Q48Agent (rows) x status (columns) avg-resolution-hours matrix.
  24. Q49Region x status matrix with both counts and revenue (interleaved).
  25. Q50Build a category x quarter revenue matrix with row & column totals.

PERCENT-OF-TOTAL PIVOTS

  1. Q51Region x status order-count with each cell as % of the region's total.
  2. Q52Month x payment_mode revenue with each cell as % of the month.
  3. Q53Brand price-band counts as % of brand total.
  4. Q54Store quarter revenue as % of the store's year.
  5. Q55Category month units as % of the category's annual units.
  6. Q56Agent priority counts as % of the agent's tickets.
  7. Q57Region tier counts as % of the region's customers.
  8. Q58Product rating counts as % of the product's reviews.
  9. Q59Courier on-time % per courier (FILTER ratio).
  10. Q60Department role headcount as % of department.
  11. Q61Store weekday revenue as % of the store's week.
  12. Q62Region status counts as % of the GRAND total (column-total denominator).
  13. Q63Brand tier revenue as % of brand total.
  14. Q64Category returned units as % of category delivered units (return rate).
  15. Q65Month status counts as % of all orders that month AND all-time.
  16. Q66Warehouse category stock as % of warehouse total.
  17. Q67Cohort following-year orders as % of cohort size (retention-ish).
  18. Q68Platform quarter spend as % of platform total.
  19. Q69Region season revenue as % of region annual.
  20. Q70Store net_total-band counts as % of store orders.
  21. Q71City tier as % of city customers.
  22. Q72Product month revenue as % of product annual.
  23. Q73Region x status: show count, row %, and column % together.
  24. Q74Category x quarter revenue with % of grand total per cell.
  25. Q75Build a status mix report: per region, each status count and % of region.

UNPIVOT & TIDY

  1. Q76Unpivot revenue_summary metrics (total_revenue, total_orders, avg_order_value) to rows.
  2. Q77Unpivot pay_slip components into (component, amount) per slip.
  3. Q78Unpivot a region's Q1..Q4 revenue (from a pivot) back to long.
  4. Q79Unpivot email_clicks (sent/opened/clicked) to (stage, count) per campaign.
  5. Q80Unpivot order totals (gross/discount/net) to (kind, amount).
  6. Q81Unpivot product price & cost_price to (kind, amount).
  7. Q82Unpivot tax_brackets numeric columns to long.
  8. Q83Unpivot using unnest over ARRAY[...] of labels and values.
  9. Q84Unpivot a wide monthly KPI mock (revenue_summary by date) to (date, metric, value).
  10. Q85Unpivot a customer's contact fields to (channel, value).
  11. Q86Unpivot shipment dates (shipped/delivered) to events with a type label.
  12. Q87Unpivot a store's open/close-era flags to rows.
  13. Q88Round-trip: pivot regionxstatus then unpivot; verify counts match.
  14. Q89Tidy a wide funnel (page/cart/checkout counts) into long stages.
  15. Q90Unpivot then aggregate (sum the long form) to validate the pivot.
  16. Q91Unpivot ads metrics into (metric, value) for charting.
  17. Q92Unpivot a 12-month wide row into a tidy month series.
  18. Q93Unpivot using LATERAL (VALUES ...) instead of UNION ALL.
  19. Q94Unpivot product attributes into an EAV (entity-attribute-value) shape.
  20. Q95Build a tidy (region, metric, value) table from several aggregates.
  21. Q96Unpivot then pivot on a different axis (reshape).
  22. Q97Compare UNION ALL unpivot vs unnest unpivot readability.
  23. Q98Unpivot loyalty tier thresholds (min/max points) to long.
  24. Q99Note crosstab() (tablefunc, practice) for dynamic unknown-column pivots.
  25. Q100Deliver a tidy long-format metrics table ready for a BI tool.

Interview grade, edge cases

CONCEPTUAL

  1. Q1Build a pivot with row totals, column totals, and a grand total - approaches.
  2. Q2GROUPING SETS vs UNION ALL for subtotal rows - tradeoffs.
  3. Q3ROLLUP for hierarchical subtotals (region -> store).
  4. Q4CUBE for all-combination subtotals - when it's appropriate.
  5. Q5GROUPING() to label subtotal vs detail rows.
  6. Q6Percent-of-row vs percent-of-column vs percent-of-grand-total.
  7. Q7Derived ratios inside a pivot (return rate, conversion) - placement.
  8. Q8Why FILTER keeps multi-metric pivots readable vs nested CASE.
  9. Q9When dynamic pivot is unavoidable (unknown categories) -> crosstab/app.
  10. Q10jsonb_object_agg for a dynamic key->value "pivot" (concept; JSON is Topic 24).
  11. Q11Unpivot a wide table robustly (UNION ALL vs LATERAL VALUES).
  12. Q12Avoiding double counting when pivoting over a fan-out join.
  13. Q13Pivot stability when categories may be missing in some periods.
  14. Q14Combining a pivot with a window % (Topic 17) for share columns.
  15. Q15ROLLUP ordering and NULL placement of subtotal rows.
  16. Q16Performance: pivot over a pre-aggregated CTE vs raw facts.
  17. Q17When to pivot in SQL vs ship long format to the BI layer.
  18. Q18Multi-grain pivot (counts and revenue) without two passes.
  19. Q19Pivot booleans (bool_or) into capability flags.
  20. Q20Reshape: unpivot then re-pivot on a new axis.
  21. Q21Handling division-by-zero in ratio pivots (NULLIF).
  22. Q22Grand-total reconciliation (row totals sum to grand total).
  23. Q23Why GROUPING SETS can replace several UNION ALL queries.
  24. Q24Designing a pivot that a finance team can paste into a sheet.
  25. Q25A correctness checklist for pivot+subtotal reports.

PIVOT REPORTS WITH TOTALS

  1. Q26SCENARIO: CFO wants region x payment_mode revenue with row totals, column totals, grand total.
  2. Q27Store x quarter revenue with a yearly total column.
  3. Q28Category x month units with an annual total column and monthly total row.
  4. Q29Region x status order counts with grand total (GROUPING SETS).
  5. Q30Brand x price-band counts with brand totals.
  6. Q31Agent x priority ticket counts with agent totals and grand total.
  7. Q32Region x tier revenue with ROLLUP subtotals.
  8. Q33Product x rating review counts with product total.
  9. Q34Department x role headcount with department + company totals.
  10. Q35Courier x month avg-delivery matrix with overall column.
  11. Q36Region -> store revenue ROLLUP (hierarchical subtotals).
  12. Q37Category -> brand revenue ROLLUP.
  13. Q38Month x status revenue with monthly and status totals (CUBE).
  14. Q39Platform x quarter spend with platform totals.
  15. Q40Store x weekday revenue with weekend vs weekday subtotals.
  16. Q41Warehouse x category stock with warehouse totals.
  17. Q42Cohort-year x following-year orders with cohort totals.
  18. Q43City x tier customers with city totals (via addresses).
  19. Q44Region x season revenue with grand total and GROUPING() labels.
  20. Q45Brand x customer-tier revenue with totals.
  21. Q46Status x month order counts transposed with totals.
  22. Q47Product x quarter revenue with product annual total.
  23. Q48Region x payment_mode counts AND revenue interleaved with totals.
  24. Q49Reconcile: verify row totals sum to the grand total.
  25. Q50Deliver a finance-ready region x month revenue sheet with all margins.

RATIOS & GROUPING SETS

  1. Q51Region x status counts with each as % of region (row %).
  2. Q52Region x status counts with each as % of status (column %).
  3. Q53Category x month units with % of grand total per cell.
  4. Q54Return rate matrix: returned units / delivered units per category x month.
  5. Q55Conversion-ish matrix: orders / customers per region x tier.
  6. Q56Store x quarter revenue indexed to Q1 = 100 (no LAG; ratio to FILTER Q1).
  7. Q57Brand price-band counts as % of brand (row %) with brand totals.
  8. Q58Agent priority counts as % of agent tickets.
  9. Q59Region tier revenue with % of region and % of company.
  10. Q60ROLLUP region->store revenue with subtotal % of region.
  11. Q61GROUPING SETS to produce detail + region subtotal + grand total in one query.
  12. Q62CUBE over (region, status) with all margin combinations.
  13. Q63Product rating mix as % per product with avg rating column.
  14. Q64Courier on-time % per month per courier (FILTER ratio matrix).
  15. Q65Category returned/delivered with return-rate column (NULLIF guard).
  16. Q66Month status mix as % of month AND running cumulative (with window).
  17. Q67Region x tier with both count and revenue and revenue-per-customer.
  18. Q68Platform quarter spend as % of platform and % of total.
  19. Q69Store weekday revenue share (% of store week).
  20. Q70GROUPING() to tag which rows are subtotals in a ROLLUP.
  21. Q71Department role headcount % with company total row.
  22. Q72Cohort retention matrix as % of cohort size (GROUPING SETS friendly).
  23. Q73Warehouse category stock % of warehouse and of company.
  24. Q74Region x season revenue with % of region annual.
  25. Q75Build a mix-and-margin report: counts, row %, subtotal, grand total.

UNPIVOT & RESHAPE

  1. Q76SCENARIO: A wide quarterly KPI export must become tidy (entity, metric, period, value).
  2. Q77Unpivot pay_slip components into long (slip, component, amount) and validate the sum = gross.
  3. Q78Unpivot revenue_summary into (date, metric, value).
  4. Q79Unpivot email_clicks into a funnel (campaign, stage, count) and compute drop-off.
  5. Q80Unpivot order totals (gross/discount/net) and verify gross-discount=net.
  6. Q81Reshape: pivot regionxstatus, then unpivot back, prove round-trip equality.
  7. Q82Unpivot a 12-month wide row into a tidy month series via LATERAL VALUES.
  8. Q83Unpivot product price/cost/margin into (kind, amount).
  9. Q84Unpivot tax_brackets to long and chart the rate curve.
  10. Q85Unpivot ads metrics into (metric, value) per campaign.
  11. Q86Build an EAV table from several customer attributes.
  12. Q87Unpivot shipment lifecycle dates into events with type labels.
  13. Q88Unpivot a pivoted matrix produced earlier (columns -> rows).
  14. Q89Tidy a funnel matrix into long stages with conversion %.
  15. Q90Unpivot loyalty tier thresholds (min/max) to long.
  16. Q91Reshape monthly wide -> long -> re-pivot by quarter.
  17. Q92Unpivot using unnest over two parallel arrays (labels, values).
  18. Q93Unpivot then GROUP BY metric to get cross-entity totals.
  19. Q94Unpivot store KPIs (revenue, orders, aov) to long for comparison.
  20. Q95Validate an unpivot: long-form sum equals wide-form total.
  21. Q96Reshape a wide cohort grid into tidy (cohort, month_since, retained).
  22. Q97Unpivot a regionxquarter pivot to (region, quarter, revenue).
  23. Q98Produce a tidy metrics table for a dashboard from 6 aggregate columns.
  24. Q99Note crosstab()/tablefunc (practice) for the dynamic-column case.
  25. Q100Deliver both a pivoted finance sheet AND its tidy long-format source.

Production scenarios, optimisation

CONCEPTUAL

  1. Q1Architect a multi-metric pivot (count, revenue, AOV) with full margins in one query.
  2. Q2GROUPING SETS vs ROLLUP vs CUBE - exact semantics and output shapes.
  3. Q3GROUPING()/GROUPING_ID() to label and order subtotal rows.
  4. Q4Dynamic pivot options: crosstab (tablefunc), jsonb_object_agg, app-side.
  5. Q5Why static SQL can't return unknown-at-plan-time columns.
  6. Q6jsonb_object_agg(key, value) as a dynamic key->value result (Topic 24 detail).
  7. Q7Avoiding fan-out double counts when pivoting over joins.
  8. Q8Combining pivot cells with window shares (Topic 17) safely.
  9. Q9Reshape pipeline design: long <-> wide and when each is canonical.
  10. Q10Performance: pre-aggregate to grain before pivoting big facts.
  11. Q11NULL vs 0 semantics in pivots and their effect on AVG.
  12. Q12Subtotal reconciliation guarantees (rows sum to grand total).
  13. Q13Pivoting time buckets (hour/day/week/month) consistently.
  14. Q14When the BI tool should pivot vs SQL (cardinality, freshness).
  15. Q15Multi-axis reporting (region x status x month) - flatten strategy.
  16. Q16EAV <-> wide conversions and their tradeoffs.
  17. Q17Ratio matrices (return rate, conversion) with safe division.
  18. Q18CUBE explosion risk on high-cardinality dimensions.
  19. Q19Materializing a pivot into an MV (Topic 25) for dashboards.
  20. Q20Stable column sets across periods (carry zero categories).
  21. Q21Index-to-base (=100) matrices without LAG (ratio to a FILTERed base).
  22. Q22Pivot + percentile (Topic 22) combos for KPI strips.
  23. Q23Generating the dynamic crosstab column list from a query (two-step).
  24. Q24Validating a reshape pipeline (round-trip equality + totals).
  25. Q25A staff-level reporting checklist (grain, margins, ratios, freshness).

MULTI-METRIC MATRICES

  1. Q26SCENARIO: Build a region x month matrix with revenue, orders, AOV, and full margins.
  2. Q27Store x quarter: revenue + units + return-rate cells.
  3. Q28Category x month: revenue, units, and % of grand total per cell.
  4. Q29Region x status: count, revenue, and revenue-per-order interleaved.
  5. Q30Brand x tier: revenue, customers, revenue-per-customer.
  6. Q31Agent x priority: ticket count, avg resolution hrs, SLA-breach %.
  7. Q32Product x month: units, revenue, and margin.
  8. Q33Courier x month: shipments, on-time %, avg delivery days.
  9. Q34Department x role: headcount, avg salary, payroll share.
  10. Q35Region x season: revenue, orders, and YoY-base ratio.
  11. Q36Platform x quarter: spend, share %, and cumulative.
  12. Q37Warehouse x category: stock, turns, and % of warehouse.
  13. Q38Cohort x month-since: customers, revenue, retention %.
  14. Q39City x tier: customers, spend, spend-per-customer.
  15. Q40Month x status: revenue with ROLLUP subtotals and labels.
  16. Q41Region x payment_mode: revenue with CUBE all-margins.
  17. Q42Category -> brand revenue ROLLUP with subtotal %.
  18. Q43Store x weekday: revenue, orders, and weekend subtotal.
  19. Q44Region x tier: revenue, % of region, % of company (3 ratios).
  20. Q45Product rating mix with avg rating and negative-review %.
  21. Q46Region x month with revenue, MoM-base (FILTER prev month), and growth %.
  22. Q47Brand x price-band counts, revenue, and contribution %.
  23. Q48Multi-metric finance sheet: region x quarter revenue, cost, margin %.
  24. Q49Reconcile every margin in a multi-metric matrix.
  25. Q50Deliver an exec multi-metric matrix (region x month, 4 metrics, full margins).

DYNAMIC-PIVOT STRATEGY

  1. Q51SCENARIO: Categories change over time - design a dynamic payment_mode pivot strategy.
  2. Q52Two-step dynamic pivot: query the distinct columns, then build the SQL (describe).
  3. Q53(practice) crosstab() from tablefunc for region x dynamic-status.
  4. Q54jsonb_object_agg(status, cnt) per region as a dynamic "pivot" (Topic 24 detail).
  5. Q55Compare static FILTER pivot vs jsonb dynamic result.
  6. Q56Generate the column list for a month pivot dynamically (distinct months).
  7. Q57Dynamic brand pivot - why app-side or crosstab is required.
  8. Q58(practice) crosstab with a category source query + values query.
  9. Q59jsonb_object_agg for product -> units map per brand.
  10. Q60Handle new categories gracefully (json grows; static pivot doesn't).
  11. Q61Build a key->value JSON per customer for a flexible report (Topic 24 preview).
  12. Q62Decide crosstab vs jsonb vs app pivot for a dashboard (tradeoffs).
  13. Q63Dynamic pivot of order_status counts per month as JSON.
  14. Q64Dynamic region pivot of revenue as JSON keyed by region.
  15. Q65Convert a jsonb "pivot" back to columns when the keys are known.
  16. Q66(practice) crosstab for agent x priority counts.
  17. Q67Dynamic tier pivot per region as JSON with totals.
  18. Q68Strategy memo: when to push dynamic pivot to the BI tool.
  19. Q69jsonb_object_agg with COALESCE for missing categories.
  20. Q70Two-query approach: distinct columns + a generated pivot statement (outline).
  21. Q71Dynamic month-bucket pivot for a rolling 12-month window.
  22. Q72Compare maintainability: static FILTER vs dynamic crosstab.
  23. Q73jsonb per store of status->count for a flexible front end.
  24. Q74Validate a dynamic pivot's totals against a static aggregate.
  25. Q75Recommend a dynamic-pivot approach for the analytics platform.

RESHAPE PIPELINES

  1. Q76SCENARIO: Ingest a wide quarterly KPI export -> tidy long -> re-pivot by metric.
  2. Q77Unpivot pay_slips to components, validate sum=gross, re-pivot by month.
  3. Q78Unpivot revenue_summary to (date, metric, value), then pivot metricxmonth.
  4. Q79Reshape email_clicks into a funnel long form with stage drop-off.
  5. Q80Round-trip a regionxstatus pivot (wide->long->wide) and prove equality.
  6. Q81Build an EAV from customer attributes, then pivot back selected keys.
  7. Q82Unpivot order totals, aggregate by kind, re-pivot by month.
  8. Q83Reshape a 12-month wide row to long via LATERAL VALUES then bucket by quarter.
  9. Q84Unpivot ads metrics, join to campaigns, re-pivot by platform.
  10. Q85Tidy a cohort grid (wide month-since columns) into long and validate.
  11. Q86Reshape store KPIs (revenue/orders/aov) long, rank within metric (Topic 16).
  12. Q87Unpivot tax_brackets, chart the curve, re-pivot by rate band.
  13. Q88Convert a jsonb pivot to long via jsonb_each (Topic 24 preview).
  14. Q89Pipeline: facts -> pre-aggregate -> pivot -> percent-of-row -> export.
  15. Q90Reshape web funnel counts into stages with conversion %.
  16. Q91Unpivot product price/cost/margin, aggregate by kind across catalog.
  17. Q92Build a tidy (entity, metric, period, value) table from 3 sources.
  18. Q93Validate a reshape pipeline end-to-end (totals + round-trip).
  19. Q94Long->wide for a finance sheet, wide->long for a data scientist - one source.
  20. Q95Reshape regionxquarter->long->regionxyear via re-aggregation.
  21. Q96Unpivot loyalty thresholds, compute gaps between tiers.
  22. Q97Pipeline that emits both pivoted and tidy outputs from one CTE.
  23. Q98Reshape a multi-metric matrix into a tidy metric column.
  24. Q99Document the reshape pipeline for reproducibility.
  25. Q100Deliver a full reshape pipeline: ingest wide -> tidy -> curated pivots + tidy export.