top of page

Welcome
to NumpyNinja Blogs

NumpyNinja: Blogs. Demystifying Tech,

One Blog at a Time.
Millions of views. 

SQL QUERIES EVERY DATA ANALYST MUST KNOW

Nov 5
4 min read

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.


 
 

+1 (302) 200-8320

NumPy_Ninja_Logo (1).png

Numpy Ninja Inc. 8 The Grn Ste A Dover, DE 19901

© Copyright 2025 by Numpy Ninja Inc.

  • Twitter
  • LinkedIn
bottom of page