source: kdnuggets: practical sql tricks every data scientist should know
level: technical
basic sql queries with select, where, and group by handle simple aggregations, but real analytical tasks often need more advanced patterns. examples include measuring time between events, comparing rows across time, selecting top rows per group, segmenting customers, smoothing noisy data, aggregating conditionally, and detecting consecutive activity streaks. this article covers seven practical sql techniques that solve these problems using standard sql features like window functions, self-joins, and common table expressions.
the first pattern uses lag() to calculate days between successive completed transactions per customer, revealing renewal cadences. a self-join identifies customers who upgraded from starter to pro plans by comparing rows with different plan types and timestamps. row_number() inside a cte retrieves each customer's highest transaction, with tiebreakers for equal amounts. ntile(4) segments customers into spend quartiles based on total completed transaction value, avoiding hardcoded thresholds. a rolling window with rows between 2 preceding and current row computes a 3-month moving average of monthly revenue, smoothing volatility.
the filter clause enables multiple conditional aggregations in one query, such as summing completed revenue, refunded revenue, and counting failed transactions per month. finally, a window function technique detects consecutive active months by assigning streak identifiers based on month gaps, then grouping to find streak start, end, and length. these patterns work in standard sql and can replace multi-step python transformations, making data analysis more efficient and scalable.
why it matters: these sql patterns reduce reliance on external tools for common analytical tasks, enabling faster, more maintainable data pipelines directly in the database.
source: kdnuggets: practical sql tricks every data scientist should know