SQL Queries You'll Use Every Day (But Nobody Teaches in Tutorials)

SQL tutorials teach you SELECT, INSERT, UPDATE, DELETE. Then they stop. But production SQL is 80% window functions, CTEs, and conditional aggregation. Here are the patterns I use daily. 1. CTEs (Common Table Expressions) The single most useful SQL feature: -- Find users who signed up last month and made a purchase WITH recent_users AS ( SELECT id , email , created_at FROM users WHERE created_at > CURRENT_DATE - INTERVAL '30 days' ), purchasers AS ( SELECT DISTINCT user_id FROM orders WHERE created_at > CURRENT_DATE - INTERVAL '30 days' ) SELECT r . email , r . created_at FROM recent_users r JOIN purchasers p ON r . id = p . user_id ; CTEs make complex queries readable. Each WITH block is a named subquery you can reference. 2. Window Functions Running Total SELECT date , revenue , SUM ( revenue ) OVER ( ORDER BY date ) as running_total FROM daily_sales ; Rank Within Groups -- Top 3 products per category SELECT * FROM ( SELECT name , category , sales , ROW_NUMBER () OVER ( PARTITION BY c