SQL QUERIES EVERY DATA ANALYST MUST KNOW
Introduction
SQL (Structured Query Language) helps analysts retrieve, filter, clean, combine, summarize, and analyze relational data. from SELECT to window functions and customer segmentation. All examples use fictional retail transactions and PostgreSQL syntax.
Sample Data and Setup
Order ID | Region | Category | Sales | Profit |
O101 | West | Technology | 1200 | 240 |
O102 | East | Furniture | 850 | 95 |
O103 | Central | Office Supplies | 430 | -25 |
O104 | South | Technology | 1550 | 310 |
O105 | West | Furniture | 720 | 110 |
O106 | East | Technology | 980 | 175 |
CREATE TABLE sales_data (order_id VARCHAR(20), order_date DATE,
customer_id VARCHAR(20), region VARCHAR(50), category VARCHAR(50),
sales NUMERIC(12,2), profit NUMERIC(12,2), discount NUMERIC(5,2));
INSERT INTO sales_data VALUES
('O101','2025-01-10','C001','West','Technology',1200,240,0.10),
('O102','2025-02-15','C002','East','Furniture',850,95,0.15),
('O103','2025-03-05','C003','Central','Office Supplies',430,-25,0.20),
('O104','2025-04-20','C001','South','Technology',1550,310,0.05),
('O105','2025-05-12','C004','West','Furniture',720,110,0.10),
('O106','2025-06-18','C005','East','Technology',980,175,0.10);
1. Basic SQL
1. SELECT — Retrieve Data
SELECT order_id, region, category, sales FROM sales_data;
Choose specific columns rather than returning everything.
2. WHERE — Filter Records
SELECT * FROM sales_data WHERE sales > 1000;
Find orders with sales above a specified threshold.
3. ORDER BY — Sort Results
SELECT order_id, sales FROM sales_data ORDER BY sales DESC;
Identify the highest-value orders first.
4. DISTINCT — Unique Values
SELECT DISTINCT region FROM sales_data ORDER BY region;
Explore the unique categories present in a field.
5. CASE WHEN — Create Categories
SELECT order_id, sales, CASE WHEN sales >= 1000 THEN 'High'
WHEN sales >= 500 THEN 'Medium' ELSE 'Low' END AS sales_category
FROM sales_data;
Create business-defined performance bands.
2 · Aggregation
6. COUNT, SUM, AVG — Business KPIs
SELECT COUNT(*) AS total_records, COUNT(DISTINCT order_id) AS total_orders,
SUM(sales) AS total_sales, SUM(profit) AS total_profit,
ROUND(AVG(sales),2) AS average_sales FROM sales_data;
Sample: 6 orders, $5,730 sales, $905 profit, and $955 average sales per row.
7. GROUP BY — Category Summary
SELECT category, SUM(sales) AS total_sales, SUM(profit) AS total_profit
FROM sales_data GROUP BY category ORDER BY total_sales DESC;
Technology has the largest sales total in the sample.

8. HAVING — Filter Groups
SELECT region, SUM(sales) AS total_sales FROM sales_data
GROUP BY region HAVING SUM(sales) > 1000;
WHERE filters rows; HAVING filters aggregated groups.
9. Profit Margin — Derived KPI
SELECT region, SUM(sales) AS total_sales, SUM(profit) AS total_profit,
ROUND(100.0*SUM(profit)/NULLIF(SUM(sales),0),2) AS profit_margin_pct
FROM sales_data GROUP BY region ORDER BY profit_margin_pct DESC NULLS LAST;
NULLIF avoids division by zero. South leads the small sample in profit margin.

10. DATE_TRUNC — Monthly Trend
SELECT DATE_TRUNC('month',order_date)::date AS sales_month,
SUM(sales) AS monthly_sales FROM sales_data GROUP BY 1 ORDER BY 1;
Summarize transactions into month-level totals.

Part 3 · Joins and Subqueries
11. INNER JOIN — Matching Records
SELECT s.order_id,c.customer_name,c.segment,s.sales
FROM sales_data s INNER JOIN customers c ON s.customer_id=c.customer_id;
Return only sales with a matching customer record.
CREATE TABLE customers (customer_id VARCHAR(20) PRIMARY KEY, customer_name VARCHAR(100), segment VARCHAR(50));
INSERT INTO customers VALUES ('C001','Ava','Consumer'),('C002','Noah','Corporate'),('C003','Mia','Home Office'),('C004','Liam','Consumer'),('C005','Emma','Corporate');
12. LEFT JOIN — Retain All Sales
SELECT s.order_id,s.customer_id,c.customer_name,s.sales
FROM sales_data s LEFT JOIN customers c ON s.customer_id=c.customer_id;
Use a LEFT JOIN and filter c.customer_id IS NULL to find unmatched records.
13. Subquery — Above Average Orders
SELECT order_id,category,sales FROM sales_data
WHERE sales > (SELECT AVG(sales) FROM sales_data) ORDER BY sales DESC;
Compare each order against an aggregate benchmark.
Part 4 · Advanced Analytics
14. ROW_NUMBER — Unique Sequence
SELECT order_id,sales,ROW_NUMBER() OVER
(ORDER BY sales DESC,order_id) AS row_number FROM sales_data;
Assign unique row positions in a deterministic order.
15. RANK and DENSE_RANK
SELECT order_id,sales,RANK() OVER (ORDER BY sales DESC) AS sales_rank,
DENSE_RANK() OVER (ORDER BY sales DESC) AS dense_sales_rank
FROM sales_data;
16. CTE — Organize a Query
WITH regional_sales AS (SELECT region,SUM(sales) AS total_sales
FROM sales_data GROUP BY region)
SELECT region,total_sales,RANK() OVER
(ORDER BY total_sales DESC) AS region_rank
FROM regional_sales ORDER BY region_rank;
Use named intermediate results for readable analytical logic.
17. Running Total — Cumulative Sales
SELECT order_date,order_id,sales, SUM(sales) OVER
(ORDER BY order_date,order_id ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS cumulative_sales
FROM sales_data ORDER BY order_date,order_id;
Track accumulated sales over time.

18. LAG — Previous Month
WITH monthly_sales AS (SELECT DATE_TRUNC('month',order_date)::date
AS sales_month,SUM(sales) AS total_sales FROM sales_data GROUP BY 1)
SELECT sales_month,total_sales,LAG(total_sales) OVER
(ORDER BY sales_month) AS previous_month_sales
FROM monthly_sales ORDER BY sales_month;
Compare observed periods; add a calendar table if months may be missing.
19. NTILE — Customer Quartiles
WITH customer_sales AS (SELECT customer_id,SUM(sales) AS total_spending
FROM sales_data GROUP BY customer_id)
SELECT customer_id,total_spending,NTILE(4) OVER
(ORDER BY total_spending DESC,customer_id) AS spending_quartile
FROM customer_sales;
Divide customers into roughly equal ranked groups; this sample is too small for meaningful segmentation.
20. LIMIT — Top N Categories
SELECT category,SUM(sales) AS total_sales FROM sales_data
GROUP BY category ORDER BY total_sales DESC LIMIT 3;
Return the three categories with the highest sales.
SQL Query Cheat Sheet
Technique | Analytical purpose |
SELECT / WHERE | Retrieve and filter rows |
GROUP BY / HAVING | Summarize and filter groups |
JOIN | Combine related tables |
CASE | Create business classifications |
CTE / Subquery | Structure multi-step queries |
ROW_NUMBER / RANK | Order and rank observations |
SUM OVER / LAG | Running totals and period comparisons |
NTILE / LIMIT | Segment and identify top performers |
Common Mistakes to Avoid
Check duplicate keys before deleting records; validate join cardinality to prevent inflated totals; handle NULL values; protect divisions with NULLIF; confirm data types; and reconcile summary metrics against source tables. Window functions should use meaningful ordering, and conclusions should reflect sample size.


