SQL Analytics Queries Guide for Data Science Work
SQL is the most important data skill you can learn. Every organization stores data in relational databases, and SQL is the universal language for accessing that data. According to the 2024 Stack Overflow Survey, SQL is the third most commonly used language across all developers and the most used language in data roles, with 56 percent of data professionals reporting daily SQL use. Despite the rise of NoSQL databases and cloud data warehouses like Snowflake and BigQuery, SQL remains the common denominator that every data practitioner must master to extract value from organizational data assets.
Data science SQL goes beyond basic CRUD operations of SELECT, INSERT, UPDATE, and DELETE. Analytics queries involve complex aggregations for business reporting, window functions for time-series analysis and ranking, common table expressions for readable multi-step transformations, and statistical calculations like percentile distributions and z-scores. These advanced SQL skills distinguish data analysts who can answer business questions directly from the database from those who need to export data to Python or R for every non-trivial analysis.
SELECT and Efficient Filtering
The SELECT statement is the foundation of SQL analytics. It retrieves data from tables and applies filters, transformations, and sorting to produce the required result set. The WHERE clause filters rows before any aggregation, which is the most important performance optimization — filtering early reduces the data volume that must be processed in subsequent operations like joins, aggregations, and sorting.
WHERE Clause Best Practices
For date filtering, use BETWEEN for inclusive ranges. Avoid applying functions to indexed columns in WHERE clauses, as this prevents index usage and forces sequential scans. Instead of using YEAR on a date column, use direct date comparisons. This sargable predicate allows the database to perform range scans on B-tree indexes, dramatically improving query performance on large tables that may contain millions of rows.
Pattern Matching
LIKE performs simple pattern matching with percent representing any sequence of characters and underscore representing a single character. For case-insensitive matching, PostgreSQL provides ILIKE while MySQL’s LIKE is case-insensitive by default on most collations. For complex patterns, regular expressions provide more powerful matching at the cost of more computation.
JOINs for Analytics
Relational databases normalize data across tables to reduce redundancy through foreign key relationships. JOINs reconstruct the complete picture by combining related tables based on these relationships. INNER JOIN returns only rows with matching keys in both tables and is the most common join type, used when you need only records with complete relationships such as orders that have valid customer records.
LEFT JOIN returns all rows from the left table with NULLs where no match exists on the right, essential for retention analysis and finding records missing relationships. The anti-join pattern with WHERE right key IS NULL finds records in the left table that lack corresponding records in the right — for example, customers who have never placed an order.
Aggregation and Grouping
Aggregate functions summarize data at a higher level of abstraction. COUNT, SUM, AVG, MIN, and MAX are the foundational building blocks that convert detailed transaction data into business metrics. GROUP BY groups rows by shared values and applies aggregate functions per group, while HAVING filters groups after aggregation using conditions on the aggregated values.
Understanding the difference between WHERE and HAVING is essential for writing correct and efficient queries. WHERE filters rows before aggregation, reducing the data volume that must be grouped. HAVING filters groups after aggregation, operating on the results of aggregate functions. Using WHERE for pre-aggregation filtering is always more efficient than filtering in HAVING because it reduces the data volume that the database must process during the group by operation.
Window Functions for Advanced Analytics
Window functions perform calculations across a set of rows related to the current row while preserving individual row details. They are the key feature that separates basic SQL from analytical SQL, enabling complex calculations that would otherwise require self-joins, subqueries, or procedural code.
Ranking Functions
ROW_NUMBER, RANK, and DENSE_RANK assign positions within partitions with different behaviors for ties. ROW_NUMBER assigns unique sequential integers even for tied values, making it suitable for pagination and deduplication. RANK leaves gaps in the sequence for tied values, while DENSE_RANK assigns consecutive ranks without gaps. These functions are essential for top-N-per-group queries, customer segmentation tiers, and leaderboard generation.
Running Totals and Moving Averages
Window functions with the ORDER BY clause compute running totals through cumulative sums and moving averages through frame specifications. A seven-day moving average defined by window frame clause smoothing daily fluctuations to reveal underlying trends. The frame clause controls which rows are included in the window, ranging from unbounded preceding for running totals to specific ranges for moving calculations.
Lag and Lead for Time Series
The LAG and LEAD functions access data from previous or subsequent rows within the same result set. These are essential for computing period-over-period changes, such as month-over-month revenue growth or year-over-year user count comparisons. LAG retrieves a value from a specified number of rows before the current row, enabling direct calculation of differences and percentage changes within a single query without self-joins.
Common Table Expressions
CTEs make complex queries readable and maintainable by breaking them into named subqueries that can be referenced multiple times in the main query. Unlike derived subqueries that must be duplicated if referenced more than once, CTEs are defined once and referenced by name. Recursive CTEs extend this capability to hierarchical data like organizational charts, bill-of-materials expansions, and graph traversal queries.
Pivoting Data with Crosstabs
Pivoting transforms row-oriented data into column-oriented summaries, creating cross-tabulation reports that compare metrics across multiple dimensions. The CASE WHEN pattern with aggregate functions creates pivot tables in standard SQL by conditionally assigning values to columns. For example, to create a monthly sales pivot table, each month becomes a column with SUM(CASE WHEN month equals target month THEN amount ELSE 0 END) as the aggregation expression.
PostgreSQL’s tablefunc extension provides the crosstab function that automates pivot table creation, accepting a base query and a column list. MySQL and SQL Server have built-in PIVOT operators that provide native syntax for this transformation. Pivoting is essential for financial reporting where months or quarters are columns, for survey analysis where questions are rows and respondent groups are columns, and for inventory analysis where products are rows and warehouse locations are columns.
Analytical Query Patterns
Cohort Analysis
Cohort analysis tracks user groups over time to measure retention and engagement. Each cohort is defined by a shared characteristic such as signup month or acquisition channel. Tracking how each cohort behaves over subsequent periods reveals whether product changes, marketing campaigns, or market conditions are improving or degrading long-term engagement. A healthy SaaS business shows stable or improving retention across increasingly recent cohorts.
RFM Analysis
Recency, Frequency, Monetary value segmentation identifies customer segments for targeted marketing. Recency measures days since last purchase, frequency counts total purchases, and monetary value sums total spend. Customers scoring high in all three dimensions are the best customers. Low recency identifies churning customers requiring re-engagement. Low frequency identifies at-risk customers needing increased engagement. Low monetary value identifies customers needing upsell.
Query Optimization and Indexing
Index columns used in WHERE, JOIN, and ORDER BY clauses. Filter early to minimize scanned data. Select only needed columns instead of using SELECT star. Use EXPLAIN ANALYZE to understand query execution plans and identify sequential scans, nested loops, and sort operations that could benefit from indexing. For large tables, consider partitioning by date and using materialized views for expensive frequently-run aggregations.
Statistical Functions in SQL
Modern SQL databases support a range of statistical functions useful for data science workflows. PERCENTILE_CONT and PERCENTILE_DISC compute percentile values within groups, essential for understanding value distributions and setting thresholds. CORR computes Pearson correlation coefficients between two variables directly in SQL, eliminating the need to export data to Python for basic statistical analysis. STDDEV_POP and STDDEV_SAMP compute standard deviation for population and sample data respectively.
These statistical functions enable data scientists to perform initial statistical analysis directly in the database, reducing data movement and enabling faster iteration. By computing descriptive statistics at the database level, analysts can filter, transform, and summarize data without transferring raw records to application memory.
Frequently Asked Questions
What is the difference between WHERE and HAVING? WHERE filters individual rows before aggregation. HAVING filters groups after aggregation. WHERE is more efficient because it reduces data that needs to be aggregated.
How do I handle NULLs in SQL comparisons? NULL is not equal to anything including itself. Use IS NULL or IS NOT NULL for checks. Use COALESCE for default values.
What is the best way to paginate through large result sets? Use keyset pagination with WHERE id greater than last_seen_id ORDER BY id LIMIT n instead of OFFSET or LIMIT with offset.
How do I improve slow query performance? Examine execution plans with EXPLAIN ANALYZE. Add indexes, rewrite subqueries as JOINs, filter earlier, and use CTEs.
When should I use a window function instead of GROUP BY? Use GROUP BY to collapse rows. Use window functions to compute aggregates while preserving individual row details for running totals and rankings.
Pandas Guide — Python Data Analysis Guide — Data Visualization Guide