Advanced SQL performance and analysis

Sooq Analytics, a fictional product-analytics company in Jeddah, runs the reporting for an online marketplace. Its database is small by industry standards but large enough to hurt: 20,000 users, 300,000 behavioural events and 100,000 orders for 2025. The tables were created in a hurry, so only primary keys are indexed. The login service, the support tool and the operations dashboard all feel slow, and the analysts' reports keep timing out when several people run them at once.

You join as the analyst who also owns query performance. The job has two halves that always go together in practice. First, answer the business questions correctly: how activity is distributed, which categories earn the money, whether highly active users actually buy more. Second, make the important queries cheap: read their plans, add only the indexes that change the plan, and rewrite patterns that the planner cannot help (correlated subqueries, functions wrapped around indexed columns, NOT IN).

Two professional rules apply throughout. Timings are never evidence on their own: they vary between machines, caches and runs, so this project judges a plan by its shape (which scan and which nodes the planner chose) and judges a rewrite by whether it returns exactly the same rows as before. And every index has a price (storage, slower writes), so a good change adds the one index that the query needs, not five that might help. The state checks use two helper functions, plan_nodes(query) and plan_uses_index(query), which run EXPLAIN (FORMAT JSON) and read the node types; they are created for you in each task's setup.