Independence in Analysis
- Query databases without waiting for IT reports
- Validate requirements against real data
- Answer stakeholder questions in real-time
- Prototype calculations before development
- Perform ad-hoc analysis during workshops
SQL skills enable BAs to independently query databases, validate requirements, and answer business questions without waiting for reports. This guide focuses on practical BA scenarios, not database administration.
SQL appears in 70–78% of BA job postings and commands a 15–20% salary premium. But beyond career advancement, SQL enables independence in your BA work. Rather than waiting days for IT to run reports, you can query production databases directly (with appropriate read-only access), validate stakeholder estimates against actual data, prototype calculations before asking developers to implement them, and answer ad-hoc business questions during meetings.
Before accessing work databases, practice SQL fundamentals on free platforms with sample datasets. These interactive environments let you write queries, see results immediately, and learn through experimentation without risk.
sqlzoo.net — Interactive tutorials with instant feedback
khanacademy.org — Free structured course with videos
mode.com/sql-tutorial — Business-focused tutorials
Every SQL query begins with SELECT. Master the basics: which columns to return, which rows to include, and how to sort results.
Select All Columns
SELECT * FROM customers;
Select Specific Columns
SELECT customer_id, first_name, email FROM customers;
Filter with WHERE
SELECT * FROM orders WHERE order_date >= '2024-01-01';
Sort with ORDER BY
SELECT * FROM products ORDER BY price DESC;
Limit Results
SELECT * FROM customers ORDER BY created_date DESC LIMIT 100;
Retrieve Top 100 Customers by Purchase Amount
SELECT customer_id, first_name, last_name, total_purchases FROM customers ORDER BY total_purchases DESC LIMIT 100;
BA Use: Identify VIP customers for requirements prioritisation
Find Recent Orders Above Threshold
SELECT order_id, customer_id, order_total, order_date FROM orders WHERE order_total > 1000 AND order_date >= '2024-01-01' ORDER BY order_date DESC;
BA Use: Validate stakeholder claim about high-value order volumes
Search for Specific Patterns
SELECT * FROM users WHERE email LIKE '%@gmail.com' AND status = 'active';
BA Use: Identify user segments for targeted feature analysis
Real databases normalise data across multiple tables. Joins combine related data: customer information with order history, products with inventory levels, users with activity logs. Understanding joins separates basic SQL users from proficient BAs.
Returns only matching records from both tables.
SELECT c.customer_id, c.name, o.order_id, o.order_total FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id;
Result: Only customers who have placed orders
BA Use: Combine customer data with order history to analyse purchasing patterns
Returns all records from left table, matching records from right table (NULL if no match).
SELECT c.customer_id, c.name, o.order_id, o.order_total FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id;
Result: All customers, including those without orders
BA Use: Identify customers who haven't purchased (order_id IS NULL) for retention analysis
Returns all records from right table, matching records from left table.
SELECT o.order_id, o.order_date, p.product_name, p.category FROM orders o RIGHT JOIN products p ON o.product_id = p.product_id;
Result: All products, including those never ordered
BA Use: Find products with no sales for inventory optimisation recommendations
Returns all records from both tables, with NULLs where no match.
SELECT c.customer_id, c.name, o.order_id FROM customers c FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;
Result: All customers and all orders, with NULL where no relationship exists
BA Use: Comprehensive data completeness audit (less common in BA work)
Business Question: "Which customers have placed orders in the last 90 days, and what's their average order value?"
SELECT c.customer_id, c.first_name, c.last_name, COUNT(o.order_id) as order_count, AVG(o.order_total) as avg_order_value FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY) GROUP BY c.customer_id, c.first_name, c.last_name ORDER BY order_count DESC;
This query answers the business question by combining customer and order data, filtering by date range, and calculating aggregated metrics.
Aggregate functions calculate summaries across multiple rows: COUNT for totals, SUM for amounts, AVG for averages, MIN/MAX for ranges. Combined with GROUP BY, these functions power business intelligence and requirements validation.
COUNT() — Count rows. COUNT(*) includes NULLs, COUNT(column) excludes NULLs.
SELECT COUNT(*) FROM orders;
SUM() — Add values. Total sales, quantities, amounts.
SELECT SUM(order_total) FROM orders;
AVG() — Calculate average. Mean order value, average rating.
SELECT AVG(order_total) FROM orders;
MIN() / MAX() — Find minimum or maximum values.
SELECT MIN(order_date), MAX(order_date) FROM orders;
GROUP BY — Calculate aggregates per category/group.
SELECT category, COUNT(*) as product_count FROM products GROUP BY category;
HAVING — Filter groups after aggregation (WHERE filters before).
SELECT customer_id, COUNT(*) as order_count FROM orders GROUP BY customer_id HAVING COUNT(*) > 5;
Returns only customers with more than 5 orders.
SELECT CASE WHEN order_count >= 10 THEN 'Frequent' WHEN order_count >= 3 THEN 'Regular' ELSE 'Occasional' END as customer_segment, COUNT(*) as customer_count, AVG(total_spent) as avg_lifetime_value FROM (SELECT customer_id, COUNT(*) as order_count, SUM(order_total) as total_spent FROM orders GROUP BY customer_id) customer_summary GROUP BY customer_segment;
BA Value: Quantifies customer segments for targeted feature development and marketing requirements.
Subqueries (nested queries) solve complex business questions by breaking them into logical steps. Use them to filter based on aggregated data, compare against calculated values, or find records meeting criteria from another table.
Filter main query based on subquery results.
SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_date >= '2024-01-01');
Result: Customers who placed orders in 2024
Subquery references outer query (runs once per row).
SELECT customer_id, first_name, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) as order_count FROM customers c;
Result: Each customer with their order count
SELECT customer_id, first_name, last_name, email FROM customers WHERE customer_id NOT IN (SELECT DISTINCT customer_id FROM orders WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH));
Business Value: Identifies at-risk customers for retention campaign requirements. Validates stakeholder assumption about inactive user volumes.
Window functions perform calculations across related rows while keeping individual row detail. Essential for ranking analysis, running totals, moving averages, and period-over-period comparisons — common BA requirements.
Assign unique sequential number to each row.
SELECT customer_id, order_date, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) as order_sequence FROM orders;
Numbers each customer's orders chronologically.
Rank rows with tie handling.
SELECT product_name, sales_total, RANK() OVER (ORDER BY sales_total DESC) as sales_rank FROM product_sales;
RANK() skips after ties, DENSE_RANK() doesn't.
Running total within groups.
SELECT order_date, order_total, SUM(order_total) OVER (ORDER BY order_date) as running_total FROM orders;
Cumulative sales over time.
SELECT DATE_FORMAT(order_date, '%Y-%m') as month, SUM(order_total) as monthly_sales, LAG(SUM(order_total)) OVER (ORDER BY DATE_FORMAT(order_date, '%Y-%m')) as prev_month_sales, ((SUM(order_total) - LAG(SUM(order_total)) OVER (ORDER BY DATE_FORMAT(order_date, '%Y-%m'))) / LAG(SUM(order_total)) OVER (ORDER BY DATE_FORMAT(order_date, '%Y-%m'))) * 100 as pct_growth FROM orders GROUP BY DATE_FORMAT(order_date, '%Y-%m');
Business Value: Quantifies growth trends for strategic planning requirements. Validates revenue projections stakeholders provide.
Coming soon: Interactive practice environment with sample BA datasets
Free Courses
Paid Options (Optional)
Books
Coming soon: One-page reference guide covering SELECT, JOIN, aggregates, subqueries, and window functions with BA-specific examples.
Print for desk reference or save as quick lookup during analysis work.