5 SQL queries every aspiring data analyst should know cold

If you're breaking into data, SQL is your bread and butter. Forget the fancy stuff — these 5 patterns show up in literally every analyst interview and real job.

1. The Aggregation + GROUP BY

SELECT department, COUNT(*) as headcount, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

This is the backbone of every report you'll ever write.

2. The Window Function

SELECT name, salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

Once you understand window functions, you'll wonder how you ever lived without them.

3. The Self-Join for Comparisons

SELECT a.month, a.revenue, b.revenue as prev_month,
  ROUND((a.revenue - b.revenue) / b.revenue * 100, 1) as growth_pct
FROM monthly_revenue a
JOIN monthly_revenue b ON a.month = b.month + 1;

Month-over-month growth. Every exec dashboard needs this.

4. The CASE WHEN for Bucketing

SELECT
  CASE WHEN age < 25 THEN '18-24'
       WHEN age < 35 THEN '25-34'
       ELSE '35+' END as age_group,
  COUNT(*) as users
FROM customers
GROUP BY 1;

Segmentation is where insights live.

5. The CTE for Readability

WITH active_users AS (
  SELECT user_id, COUNT(*) as sessions
  FROM events
  WHERE event_date >= CURRENT_DATE - 30
  GROUP BY user_id
)
SELECT sessions, COUNT(*) as user_count
FROM active_users
GROUP BY sessions;

CTEs make complex queries readable and debuggable.

Master these and you'll handle 80% of what comes your way in an analyst role. The other 20% is just Googling — same as the rest of us.

Synapse Academy

5 SQL queries every aspiring data analyst should know cold