advancedTop 30 Scenario-Based Questions

You have a running total query that's very slow on 10M rows. How do you optimize it?

Running total: SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). For 10M rows: 1) Index on the ORDER BY column: CREATE INDEX ON orders(order_date, amount) — covering index avoids heap. 2) Partition the table by date — queries on recent data only process recent partition. 3) Incremental materialized view: instead of computing running total on query, maintain a cumul

Ready to master this question?

Generate a complete walkthrough — background, the full answer in plain language, a working code example explained line by line, a real-world scenario, common mistakes, and how this same question gets asked in different ways.

Next Step

Continue to How would you implement a job queue in PostgreSQL supporting multiple workers without losing or duplicating jobs? →← Back to all SQL questions