10 SQL Queries Every Aspiring Data Analyst Should Know

A practical SQL guide covering the queries analysts use most often when exploring, filtering, aggregating and comparing business data.

Key Takeaway: SQL is the most critical technical skill for data analysts. Writing clean, optimized queries that explore database patterns determines how effectively you can access raw commercial data.

Introduction

Structured Query Language (SQL) is the global standard language for interacting with relational database management systems. While database structures vary, the logical steps required to filter, aggregate, and join records remain identical. In this tutorial, we will explore the 10 essential SQL query patterns that every junior data analyst needs to master, utilizing realistic business scenarios.

1. Selecting & Aliasing Columns

Business Problem: A product manager requests a quick list of all active products in our store, including their prices, with user-friendly column names.


        SQL / PYTHON QUERY
        
      
SELECT 
  product_name AS item_title,
  price AS retail_price_inr
FROM products
WHERE is_active = TRUE;

Why it matters: Aliasing (using “AS”) makes downstream visualization and reporting cleaner, preventing long or system-specific database column names from displaying on dashboards.

2. Basic Filtering with WHERE & LIKE

Business Problem: Find all customers who registered with corporate Gmail accounts in the last quarter and live in Maharashtra.


        SQL / PYTHON QUERY
        
      
SELECT customer_id, name, email
FROM customers
WHERE email LIKE '%@gmail.com'
  AND region = 'Maharashtra'
  AND registration_date >= '2026-01-01';

Why it matters: The wildcard character “%” matches any sequence of characters, making it highly effective for text pattern searches.

3. Aggregation & Grouping (GROUP BY)

Business Problem: Calculate total revenue and average transaction sizes for each product category.


        SQL / PYTHON QUERY
        
      
SELECT 
  category,
  COUNT(order_id) AS total_orders,
  SUM(amount) AS category_revenue,
  ROUND(AVG(amount), 2) AS average_order_value
FROM orders
GROUP BY category;

4. Filtering Grouped Data (HAVING)

Business Problem: Identify product categories that have generated more than ₹500,000 in total revenue.


        SQL / PYTHON QUERY
        
      
SELECT 
  category,
  SUM(amount) AS category_revenue
FROM orders
GROUP BY category
HAVING SUM(amount) > 500000;

Mistake Alert: You cannot use “WHERE” to filter on aggregated values like SUM(amount). “WHERE” filters individual rows, while “HAVING” filters aggregated groups.

5. Conditional Segmenting (CASE WHEN)

Business Problem: Tag customers based on their transaction sizes for marketing categorization.


        SQL / PYTHON QUERY
        
      
SELECT 
  customer_id,
  SUM(amount) AS lifetime_spend,
  CASE 
    WHEN SUM(amount) >= 100000 THEN 'VIP'
    WHEN SUM(amount) >= 30000 THEN 'Mid-Tier'
    ELSE 'Standard'
  END AS customer_tier
FROM orders
GROUP BY customer_id;

6. Relational Joins (INNER vs. LEFT JOIN)

Business Problem: Pull order records showing the customer name next to each transaction details.


        SQL / PYTHON QUERY
        
      
SELECT 
  o.order_id,
  o.order_date,
  c.name AS customer_name,
  o.amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

Insight: An INNER JOIN only returns rows with matching IDs in both tables. A LEFT JOIN keeps all records from the left table (“orders”), even if there is no customer record on file.

7. Subqueries (Nested Queries)

Business Problem: Find all orders where the transaction value is higher than the overall average order size.


        SQL / PYTHON QUERY
        
      
SELECT order_id, customer_id, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

8. Common Table Expressions (CTEs)

Business Problem: Clean up the subquery from step 7 to calculate customer cohort metrics in a readable manner.


        SQL / PYTHON QUERY
        
      
WITH average_order AS (
  SELECT AVG(amount) AS overall_avg FROM orders
)
SELECT o.order_id, o.amount
FROM orders o, average_order ao
WHERE o.amount > ao.overall_avg;

9. Ranking with Window Functions

Business Problem: Rank the top 3 highest-valued transactions for every product category.


        SQL / PYTHON QUERY
        
      
WITH ranked_sales AS (
  SELECT 
    order_id,
    category,
    amount,
    DENSE_RANK() OVER (PARTITION BY category ORDER BY amount DESC) as sales_rank
  FROM orders
)
SELECT * FROM ranked_sales
WHERE sales_rank <= 3;

10. Running Totals (CUMULATIVE SUM)

Business Problem: Calculate the cumulative sum of monthly revenue to track yearly growth trajectory.


        SQL / PYTHON QUERY
        
      
SELECT 
  order_month,
  monthly_revenue,
  SUM(monthly_revenue) OVER (ORDER BY order_month) AS cumulative_revenue
FROM (
  SELECT DATE_TRUNC('month', order_date) AS order_month, SUM(amount) AS monthly_revenue
  FROM orders GROUP BY 1
) subquery;

Next Steps

Knowledge of SQL syntax is best consolidated by practicing on real databases. In SBS programs, we build schemas, write optimized queries, and connect results directly to Python and analytical dashboards.

Start Learning Data Analytics →