データサイエンティストが知っておくべき実用的な SQL の技
KDnuggets は、データサイエンティストが大規模データを効率的に処理・分析するために不可欠な実践的な SQL の高度テクニックとベストプラクティスを解説している。
キーポイント
複雑な集計とウィンドウ関数の活用
単純な SUM や AVG だけでなく、ROW_NUMBER や RANK などのウィンドウ関数を用いて、データセット内で相対的な順位付けや移動平均を計算する高度な手法を紹介している。
パフォーマンス最適化の重要性
大量データを扱う際のコスト削減と速度向上のために、インデックス設計、クエリ実行計画の読み方、および不要なデータスキャンを避けるための書き方を強調している。
SQL と Python/R の連携
単独で完結させるのではなく、データベース上で前処理を行い、結果のみを Python や R に渡すことで、メモリ使用量を削減し分析パイプラインの効率化を図るアプローチを推奨している。
影響分析・編集コメントを表示
影響分析
この記事は、データサイエンティストが単なる分析ツールとしての SQL 利用を超え、インフラ層でのパフォーマンス管理を意識する必要性を再認識させるものである。実践的なテクニックの提示により、大規模データ環境における分析パイプラインの効率化とコスト削減に即座に貢献できる内容となっている。
編集コメント
AI モデル開発の現場では、データの質と量が重要視されるが、その前処理段階で SQL のスキルがボトルネックになるケースが多々ある。本記事は、そうした実務上の課題を解決するための具体的な指針を提供しており、特に大規模データを扱うチームにとって即戦力となる知見である。
image**
# イントロダクション
SELECT、WHERE、GROUP BY だけに注目するのは基本的な集計には十分ですが、多くの実際の分析タスクでは、単純なクエリを超えるパターンが必要です。具体的には、連続するアクティビティストリークの検出、支出ティアによる顧客のセグメンテーション、ノイズの多い時系列データの平滑化、あるいは行をまたぐプランアップグレード経路の追跡などが挙げられます。
この記事では、基本的な範囲を超えた 7 つの実用的な SQL パターンを紹介し、実際の分析問題を解決するための技術に焦点を当てます。
# データセットの設定
ここでは、架空のサブスクリプション型ソフトウェア・アズ・ア・サービス（SaaS）企業からのサンプル顧客取引テーブルを使用します：
CREATE TABLE transactions (
transaction_id SERIAL PRIMARY KEY,
customer_id INT,
plan_type VARCHAR(20), -- 'starter', 'pro', 'enterprise'
amount NUMERIC(10,2),
status VARCHAR(20), -- 'completed', 'refunded', 'failed'
created_at TIMESTAMP
);
7 人の顧客にわたる 36 の取引からなる完全なデータセットは、2023 年 9 月から 2024 年 6 月までの期間をカバーしており、seed.sql で利用可能です。クエリに進む前に、これを必ず実行してください。
# 1. LAG() を用いたイベント間の時間の計測
LAG() と LEAD() を使えば、自己結合なしで直前または次の行の値にアクセスできます。これらは、更新間隔や顧客離脱の兆候、再エンゲージメントまでの遅延など、イベント間のギャップを計算する際に特に有用です。
タスク**: 各顧客の連続した完了取引の間隔が何日経過しているかを計算してください。
SELECT
customer_id,
created_at,
LAG(created_at) OVER (
PARTITION BY customer_id
ORDER BY created_at
) AS previous_transaction_at,
ROUND(
EXTRACT(EPOCH FROM (
created_at - LAG(created_at) OVER (
PARTITION BY customer_id
ORDER BY created_at
)
)) / 86400
) AS days_since_last
FROM transactions
WHERE status = 'completed'
ORDER BY customer_id, created_at;
出力（一部省略）:
customer_id | created_at | previous_transaction_at | days_since_last
-------------+---------------------+-------------------------+-----------------
3317 | 2024-01-03 11:02:00 | |
3317 | 2024-03-15 10:45:00 | 2024-01-03 11:02:00 | 72
3317 | 2024-05-22 09:30:00 | 2024-03-15 10:45:00 | 68
4482 | 2023-09-10 09:00:00 | |
4482 | 2023-10-10 09:00:00 | 2023-09-10 09:00:00 | 30
4482 | 2023-11-10 09:14:00 | 2023-10-10 09:00:00 | 31
4482 | 2024-01-03 09:14:00 | 2023-11-10 09:14:00 | 54
4482 | 2024-03-03 08:20:00 | 2024-01-03 09:14:00 | 60
4482 | 2024-04-03 10:00:00 | 2024-03-03 08:20:00 | 31
4482 | 2024-05-01 11:00:00 | 2024-04-03 10:00:00 | 28
...
7891 | 2024-02-01 09:00:00 | |
7891 | 2024-04-01 09:00:00 | 2024-02-01 09:00:00 | 60
7891 | 2024-05-15 09:00:00 | 2024-04-01 09:00:00 | 44
8810 | 2024-01-05 12:00:00 | |
8810 | 2024-02-05 12:00:00 | 2024-01-05 12:00:00 | 31
8810 | 2024-04-05 12:00:00 | 2024-02-05 12:00:00 | 60
(29 rows)
各顧客の最初の行では、両方の列に NULL が設定されます。これは参照できる過去のイベントが存在しないためです。EXTRACT(EPOCH ...) はタイムスタンプ間隔を秒に変換し、86400 で割ることで日数を得ます。
LEAD() 関数も同様に動作しますが、過去ではなく未来の行を参照するため、次の更新までの時間計算や、チャーン（離脱）前の最後の取引フラグ付けに有用です。
# 2. セルフジョインを用いた同一テーブル内での行間比較
**
A self-join（セルフジョイン）は、同じテーブル内の行同士を関連付けるものです。時間経過に伴う同一エンティティの 2 つのイベント（アップグレード、ダウングレード、再アクティブ化、あるいは前後のパターンなど）を比較する必要がある場合に最適なツールです。
タスク: 顧客がいつかスタータープランからプロプランへ、またはプロプランからエンタープライズプランへアップグレードした事例を見つけること。
SELECT DISTINCT t1.customer_id
FROM transactions t1
JOIN transactions t2
ON t1.customer_id = t2.customer_id
AND t1.plan_type = 'starter'
AND t2.plan_type = 'pro'
AND t2.created_at > t1.created_at
WHERE t1.status = 'completed'
AND t2.status = 'completed'
ORDER BY t1.customer_id;
出力:
customer_id
4482
6204
7891
(3 rows)
このテーブルは t1 と t2 という 2 つのエイリアスで参照されており、それぞれ同じ顧客の異なる時点における状態を表すことができます。条件式 t2.created_at > t1.created_at は時間的な順序を強制するもので、これを指定しないと、顧客がどちらのプランタイプを持っていたかに関わらず、順序を問わないマッチングが行われ、意図しない結果（例えば順序が逆の場合）も含まれてしまいます。DISTINCT 句は、アップグレード前に複数のスタート取引があったケースを統合し、重複行が発生するのを防ぎます。
この同じ構造は、プランの降格を検出したり、離脱後に再訪した顧客を見つけたり、時間順に並べる必要がある任意の 2 つの状態を比較したりする場合にも活用できます。
# 3. ROW_NUMBER() を用いたグループごとの最上位行の選択
カテゴリごとに上位 N 件の行を取得したい場合（顧客ごとの最高取引額、アカウントごとの最新イベント、コホートごとの初回購入など）、共通テーブル式 (CTE) 内で ROW_NUMBER() を使用するのが標準的なアプローチです。
タスク: 各顧客の単一の最高額完了取引を取得する。
WITH ranked AS (
SELECT
customer_id,
transaction_id,
amount,
plan_type,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY amount DESC, created_at DESC
) AS rn
FROM transactions
WHERE status = 'completed'
)
SELECT customer_id, transaction_id, amount, plan_type
FROM ranked
WHERE rn = 1
ORDER BY customer_id;
出力:
customer_id | transaction_id | amount | plan_type
-------------+----------------+--------+------------
3317 | 12 | 19.00 | starter
4482 | 8 | 299.00 | enterprise
5901 | 19 | 299.00 | enterprise
6103 | 25 | 299.00 | enterprise
6204 | 28 | 79.00 | pro
7891 | 32 | 79.00 | pro
8810 | 36 | 79.00 | pro
(7 rows)
ROW_NUMBER() は、各パーティション内でソート順が最初となる行に 1 を割り当てます。その後、外部クエリでその行のみをフィルタリングします。created_at DESC による二次ソートはタイブレーカーとして機能し、2 つの取引金額が同じ場合、より新しい方が勝者となります。
同点を含めたい場合は、ROW_NUMBER() を RANK() に置き換えてください。RANK() は同点の行に同じ番号を割り当て、次のランクをスキップします（1, 1, 3）。一方、DENSE_RANK() も同様に処理しますが、ランクをスキップしません（1, 1, 2）。
# 4. NTILE(n) を用いた支出による顧客のセグメンテーション
**
NTILE(n) は、順序付けられた行を n 個のおおよそ等しいバケットに分割し、各行にバケット番号を割り当てます。これは、ハードコーディングされた閾値なしで顧客ティアリング、支出の四分位数、または A/B 分析のためのコホート構築を行うための適切なツールです。
タスク**: 完了した取引の合計金額に基づき、顧客を支出四分位にランク付けしてください。
WITH customer_spend AS (
SELECT
customer_id,
SUM(amount) AS total_spend,
COUNT(*) AS total_transactions
FROM transactions
WHERE status = 'completed'
GROUP BY customer_id
)
SELECT
customer_id,
total_spend,
total_transactions,
NTILE(4) OVER (ORDER BY total_spend) AS spend_quartile
FROM customer_spend
ORDER BY total_spend DESC;
出力:
customer_id | total_spend | total_transactions | spend_quartile
-------------+-------------+--------------------+----------------
5901 | 1495.00 | 5 | 4
6103 | 835.00 | 5 | 3
4482 | 653.00 | 7 | 3
8810 | 237.00 | 3 | 2
6204 | 177.00 | 3 | 2
7891 | 177.00 | 3 | 1
3317 | 57.00 | 3 | 1
(7 rows)
Quartile 4 は最も支出の多い顧客層、quartile 1 は最も支出の少ない顧客層です。NTILE() 関数は支出の閾値をハードコードしないため、新規顧客が追加されるたびにバケット（グループ）が自動的に再調整されます。これにより、CASE WHEN total_spend > 500 のような静的なカットオフ値よりも堅牢性が高まります。
# 5. ローリングウィンドウを用いたノイズの多いデータの平滑化
**
ローリング（または移動）平均は月ごとの変動を平滑化し、時系列データにおけるトレンドをより読みやすくします。明示的な ROWS BETWEEN フレームを持つウィンドウ関数を使用すれば、含める期間の数を精密に制御できます。
タスク**: ノイズを平滑化するために、月次収益の 3 ヶ月ローリング平均を計算してください。
WITH monthly AS (
SELECT
DATE_TRUNC('month', created_at)::DATE AS month,
SUM(amount) AS monthly_revenue
FROM transactions
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', created_at)
)
SELECT
month,
monthly_revenue,
ROUND(AVG(monthly_revenue) OVER (
ORDER BY month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
), 2) AS revenue_3mo_avg
FROM monthly
ORDER BY month;
出力:
month | monthly_revenue | revenue_3mo_avg
-------------+-----------------+-----------------
2023-09-01 | 19.00 | 19.00
2023-10-01 | 19.00 | 19.00
2023-11-01 | 79.00 | 39.00
2024-01-01 | 275.00 | 124.33
2024-02-01 | 476.00 | 276.67
2024-03-01 | 555.00 | 435.33
2024-04-01 | 835.00 | 622.00
2024-05-01 | 775.00 | 721.67
2024-06-01 | 598.00 | 736.00
(9 rows)
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW は、ウィンドウ関数に対して現在の行とその前の 2 行を参照するよう指示します。最初の 2 行は過去の履歴がないため入力数が少なくなり、それぞれ 1 ヶ月平均および 2 ヶ月平均として機能します。
同じ ORDER BY 値を持つすべての行を含めたい場合は、ROWS を RANGE に置き換えてください（複数の行が同じタイムスタンプを共有する場合に有用です）。より長い平滑化を行うには、2 PRECEDING を 5 PRECEDING に変更して 6 ヶ月のウィンドウとします。
# 6. FILTER を用いた条件付き集計
**
FILTER を使用すると、WHERE 条件を特定の集計関数に適用でき、クエリを複数のサブクエリに分ける必要がありません。その結果、データへの単一のパスで複数の条件付き集計が可能になります。
タスク**: 月別に合計収益、返金額、失敗したトランザクション数を取得する — 1 ヶ月につき 1 行で全てを出力します。
SELECT
DATE_TRUNC('month', created_at) AS month,
SUM(amount) FILTER (WHERE status = 'completed') AS revenue_completed,
SUM(amount) FILTER (WHERE status = 'refunded') AS revenue_refunded,
COUNT(*) FILTER (WHERE status = 'failed') AS failed_count
FROM transactions
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;
出力:
月 | 完了収益 | 返金収益 | 失敗数
------------------------+-------------------+------------------+--------------
2023-09-01 00:00:00+00 | 19.00 | | 0
2023-10-01 00:00:00+00 | 19.00 | | 0
2023-11-01 00:00:00+00 | 79.00 | | 0
2024-01-01 00:00:00+00 | 275.00 | | 0
2024-02-01 00:00:00+00 | 476.00 | 79.00 | 1
2024-03-01 00:00:00+00 | 555.00 | 79.00 | 0
2024-04-01 00:00:00+00 | 835.00 | 299.00 | 0
2024-05-01 00:00:00+00 | 775.00 | | 1
2024-06-01 00:00:00+00 | 598.00 | | 2
(9 rows)
FILTER の代替手段として、3 つの独立したサブクエリを結合する方法がありますが、これはコード量が増え、可読性が低下し、多くの場合実行速度も遅くなります。なお、特定の月に一致する行がない場合、SUM with FILTER は NULL（ゼロではない）を返しますが、これは正確な結果です：実際にはその月に返金が行われていないのです。ゼロを望む場合は COALESCE(..., 0) で囲んでください。
FILTER は標準 SQL であり、PostgreSQL や BigQuery で動作します。Snowflake や一部の他のデータベースでは、SUM(CASE WHEN status = 'completed' THEN amount END) を使用してください。
# 7. ウィンドウ関数を用いた連続アクティビティストリーク検出
**
不連続のないシーケンスの特定——月ごとの欠損がないアクティブ期間、取引のある連続日数、サブスクリプションの継続期間など——は、SQL におけるややこしい問題の一つです。古典的な解決策では、再帰的 CTE を使用せずにウィンドウ関数を活用して行をストリーク（連続区間）にグループ化します。
この手法は、各アクティブな月に顧客ごとのパーティション内で順次行番号を割り当てます。月が本当に連続している場合、その行番号を月の日付から減算すると、ストリーク内のすべての月で同じ定数値が得られます。欠損が生じると、この定数が崩れます。
タスク**: 各顧客の連続したアクティブな月（少なくとも 1 つの完了済み取引がある月）を特定すること。
WITH monthly_activity AS (
SELECT
customer_id,
DATE_TRUNC('month', created_at)::DATE AS active_month
FROM transactions
WHERE status = 'completed'
GROUP BY customer_id, DATE_TRUNC('month', created_at)
),
with_prev AS (
SELECT
customer_id,
active_month,
LAG(active_month) OVER (
PARTITION BY customer_id
ORDER BY active_month
) AS prev_month
FROM monthly_activity
),
streak_groups AS (
SELECT
customer_id,
active_month,
SUM(CASE WHEN active_month = prev_month + INTERVAL '1 month' THEN 0 ELSE 1 END)
OVER (PARTITION BY customer_id ORDER BY active_month) AS streak_id
FROM with_prev
),
streaks AS (
SELECT
customer_id,
streak_id,
MIN(active_month) AS streak_start,
MAX(active_month) AS streak_end,
COUNT(*) AS streak_length_months
FROM streak_groups
GROUP BY customer_id, streak_id
)
SELECT customer_id, streak_start, streak_end, streak_length_months
FROM streaks
ORDER BY customer_id, streak_start;
customer_id | streak_start | streak_end | streak_length_months
-------------+--------------+------------+----------------------
3317 | 2024-01-01 | 2024-01-01 | 1
3317 | 2024-03-01 | 2024-03-01 | 1
3317 | 2024-05-01 | 2024-05-01 | 1
4482 | 2023-09-01 | 2023-11-01 | 3
4482 | 2024-01-01 | 2024-01-01 | 1
4482 | 2024-03-01 | 2024-05-01 | 3
5901 | 2024-02-01 | 2024-06-01 | 5
6103 | 2024-01-01 | 2024-04-01 | 4
6103 | 2024-06-01 | 2024-06-01 | 1
6204 | 2024-01-01 | 2024-01-01 | 1
6204 | 2024-03-01 | 2024-03-01 | 1
6204 | 2024-05-01 | 2024-05-01 | 1
7891 | 2024-02-01 | 2024-02-01 | 1
7891 | 2024-04-01 | 2024-05-01 | 2
8810 | 2024-01-01 | 2024-02-01 | 2
8810 | 2024-04-01 | 2024-04-01 | 1
(16 rows)
# クイックリファレンス
**
これらのパターンは、データベース固有の機能に依存せず、標準 SQL で動作します。また、リテンション分析（顧客維持率分析）、アップグレードファネル追跡、収益レポート作成などの分析ワークフローで頻繁に登場します。
ヒント
使用タイミング
LAG() / LEAD()
イベント間の時間間隔、またはエンティティごとの前後比較
自己結合
状態間の遷移（アップグレード、再アクティブ化）の検出
ROW_NUMBER()
グループごとの上位 N 行、重複排除
NTILE(n)
支出や活動レベルによる顧客セグメンテーション
ローリングウィンドウ (ROWS BETWEEN)
ノイズの多い時系列データの平滑化、移動平均
FILTER
1 クエリパス内での複数の条件付き集計
連続するストリーク検出
サブスクリプションの継続期間、リテンション分析、セッション間のギャップ
これらに慣れれば、多くの場合 Python で多段階のデータ変換として処理される作業を、単一の SQL クエリでより明確かつ効率的に表現できるようになります。
Bala Priya C はインド出身のエンジニア兼テクニカルライターです。数学、プログラミング、データサイエンス、コンテンツ制作が交差する領域での作業を好んでいます。彼女の関心分野および専門知識には、DevOps、データサイエンス、自然言語処理が含まれます。読書、執筆、コーディング、そしてコーヒーを楽しむのが好きです。現在、チュートリアル、ハウツーガイド、意見記事などを執筆することで、開発者コミュニティに知識を共有し、学ぼうとしています。また、魅力的なリソースの概要やコーディングチュートリアルも作成しています。
原文を表示
**
# Introduction
Focusing only on SELECT, WHERE, and GROUP BY is enough for basic aggregation, but many real analytical tasks require patterns that go beyond simple queries. Examples include detecting consecutive activity streaks, segmenting customers by spend tier, smoothing noisy time-series data, or tracing plan upgrade paths across rows.
This article walks through 7 practical SQL patterns beyond the basics, focusing on techniques that solve real analytical problems.
# Setting Up the Dataset
We'll use a sample customer transactions table from a fictional subscription software as a service (SaaS) company:
CREATE TABLE transactions (
transaction_id SERIAL PRIMARY KEY,
customer_id INT,
plan_type VARCHAR(20), -- 'starter', 'pro', 'enterprise'
amount NUMERIC(10,2),
status VARCHAR(20), -- 'completed', 'refunded', 'failed'
created_at TIMESTAMP
);The full dataset of 36 transactions across 7 customers, spanning September 2023 through June 2024, is available in seed.sql. Run it before you move on to the queries.
# 1. Measuring Time Between Events with LAG()
LAG() and LEAD() let you access a previous or next row's value without a self-join. They're particularly useful for calculating gaps between events like renewal cadence, churn signals, and re-engagement delays.
Task**: Calculate how many days elapsed between each customer's successive completed transactions.
SELECT
customer_id,
created_at,
LAG(created_at) OVER (
PARTITION BY customer_id
ORDER BY created_at
) AS previous_transaction_at,
ROUND(
EXTRACT(EPOCH FROM (
created_at - LAG(created_at) OVER (
PARTITION BY customer_id
ORDER BY created_at
)
)) / 86400
) AS days_since_last
FROM transactions
WHERE status = 'completed'
ORDER BY customer_id, created_at;Output (truncated):
customer_id | created_at | previous_transaction_at | days_since_last
-------------+---------------------+-------------------------+-----------------
3317 | 2024-01-03 11:02:00 | |
3317 | 2024-03-15 10:45:00 | 2024-01-03 11:02:00 | 72
3317 | 2024-05-22 09:30:00 | 2024-03-15 10:45:00 | 68
4482 | 2023-09-10 09:00:00 | |
4482 | 2023-10-10 09:00:00 | 2023-09-10 09:00:00 | 30
4482 | 2023-11-10 09:14:00 | 2023-10-10 09:00:00 | 31
4482 | 2024-01-03 09:14:00 | 2023-11-10 09:14:00 | 54
4482 | 2024-03-03 08:20:00 | 2024-01-03 09:14:00 | 60
4482 | 2024-04-03 10:00:00 | 2024-03-03 08:20:00 | 31
4482 | 2024-05-01 11:00:00 | 2024-04-03 10:00:00 | 28
...
7891 | 2024-02-01 09:00:00 | |
7891 | 2024-04-01 09:00:00 | 2024-02-01 09:00:00 | 60
7891 | 2024-05-15 09:00:00 | 2024-04-01 09:00:00 | 44
8810 | 2024-01-05 12:00:00 | |
8810 | 2024-02-05 12:00:00 | 2024-01-05 12:00:00 | 31
8810 | 2024-04-05 12:00:00 | 2024-02-05 12:00:00 | 60
(29 rows)The first row per customer always has NULL for both columns — there's no prior event to reference. EXTRACT(EPOCH ...) converts the timestamp interval to seconds; dividing by 86400 gives days.
LEAD() works the same way but looks forward instead of backward, making it useful for calculating time-to-next-renewal or flagging the last transaction before churn.
# 2. Comparing a Row to Other Rows in the Same Table with a Self-Join
**
A self-join** relates rows within the same table to each other. It's the right tool when you need to compare two events for the same entity across time — upgrades, downgrades, re-activations, or any before/after pattern.
Task: Find customers who upgraded from starter to pro (or pro to enterprise) at any point.
SELECT DISTINCT t1.customer_id
FROM transactions t1
JOIN transactions t2
ON t1.customer_id = t2.customer_id
AND t1.plan_type = 'starter'
AND t2.plan_type = 'pro'
AND t2.created_at > t1.created_at
WHERE t1.status = 'completed'
AND t2.status = 'completed'
ORDER BY t1.customer_id;Output:
customer_id
-------------
4482
6204
7891
(3 rows)The table is aliased twice (t1, t2) so each alias can represent a different point in time for the same customer. The condition t2.created_at > t1.created_at enforces temporal order — without it, you'd match customers who simply had both plan types in any order, including the wrong one. DISTINCT collapses cases where a customer had multiple starter transactions before upgrading, which would otherwise produce duplicate rows.
This same structure works for detecting downgrades, finding customers who churned and came back, or comparing any two states that need to be ordered by time.
# 3. Selecting the Top Row per Group with ROW_NUMBER()
**
When you need the top-N rows per category — highest transaction per customer, most recent event per account, first purchase per cohort — ROW_NUMBER() inside a common table expression (CTE) is the standard approach.
Task**: Get each customer's single highest completed transaction.
WITH ranked AS (
SELECT
customer_id,
transaction_id,
amount,
plan_type,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY amount DESC, created_at DESC
) AS rn
FROM transactions
WHERE status = 'completed'
)
SELECT customer_id, transaction_id, amount, plan_type
FROM ranked
WHERE rn = 1
ORDER BY customer_id;Output:
customer_id | transaction_id | amount | plan_type
-------------+----------------+--------+------------
3317 | 12 | 19.00 | starter
4482 | 8 | 299.00 | enterprise
5901 | 19 | 299.00 | enterprise
6103 | 25 | 299.00 | enterprise
6204 | 28 | 79.00 | pro
7891 | 32 | 79.00 | pro
8810 | 36 | 79.00 | pro
(7 rows)ROW_NUMBER() assigns 1 to the row that sorts first within each partition. The outer query then filters to only those rows. The secondary sort on created_at DESC acts as a tiebreaker; when two transactions have the same amount, the more recent one wins.
If you want ties included rather than broken, swap ROW_NUMBER() for RANK(). RANK() assigns the same number to tied rows and skips the next rank (1, 1, 3), while DENSE_RANK() does the same without skipping (1, 1, 2).
# 4. Segmenting Customers by Spend with NTILE(n)
**
NTILE(n) divides ordered rows into n roughly equal buckets and assigns each row a bucket number. It's the right tool for customer tiering, spend quartiles, or building cohorts for A/B analysis without hardcoding thresholds.
Task**: Rank customers into spend quartiles based on their total completed transaction value.
WITH customer_spend AS (
SELECT
customer_id,
SUM(amount) AS total_spend,
COUNT(*) AS total_transactions
FROM transactions
WHERE status = 'completed'
GROUP BY customer_id
)
SELECT
customer_id,
total_spend,
total_transactions,
NTILE(4) OVER (ORDER BY total_spend) AS spend_quartile
FROM customer_spend
ORDER BY total_spend DESC;Output:
customer_id | total_spend | total_transactions | spend_quartile
-------------+-------------+--------------------+----------------
5901 | 1495.00 | 5 | 4
6103 | 835.00 | 5 | 3
4482 | 653.00 | 7 | 3
8810 | 237.00 | 3 | 2
6204 | 177.00 | 3 | 2
7891 | 177.00 | 3 | 1
3317 | 57.00 | 3 | 1
(7 rows)Quartile 4 is your highest spenders; quartile 1 is your lowest. NTILE() doesn't hardcode spend thresholds, so the buckets recalibrate automatically as new customers are added. This makes it more robust than static cutoffs like CASE WHEN total_spend > 500.
# 5. Smoothing Noisy Data with a Rolling Window
**
A rolling (or moving) average smooths out month-to-month volatility, making trends in time-series data much easier to read. Window functions with an explicit ROWS BETWEEN frame give you precise control over how many periods to include.
Task**: Calculate a 3-month rolling average of monthly revenue to smooth out noise.
WITH monthly AS (
SELECT
DATE_TRUNC('month', created_at)::DATE AS month,
SUM(amount) AS monthly_revenue
FROM transactions
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', created_at)
)
SELECT
month,
monthly_revenue,
ROUND(AVG(monthly_revenue) OVER (
ORDER BY month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
), 2) AS revenue_3mo_avg
FROM monthly
ORDER BY month;Output:
month | monthly_revenue | revenue_3mo_avg
-------------+-----------------+-----------------
2023-09-01 | 19.00 | 19.00
2023-10-01 | 19.00 | 19.00
2023-11-01 | 79.00 | 39.00
2024-01-01 | 275.00 | 124.33
2024-02-01 | 476.00 | 276.67
2024-03-01 | 555.00 | 435.33
2024-04-01 | 835.00 | 622.00
2024-05-01 | 775.00 | 721.67
2024-06-01 | 598.00 | 736.00
(9 rows)ROWS BETWEEN 2 PRECEDING AND CURRENT ROW tells the window function to look at the current row and the two rows before it. The first two rows use fewer inputs since there's no prior history, so they act as a 1-month and 2-month average respectively.
Swap ROWS for RANGE if you want to include all rows with the same ORDER BY value (useful when multiple rows share a timestamp). For longer smoothing, change 2 PRECEDING to 5 PRECEDING for a 6-month window.
# 6. Aggregating Conditionally with FILTER
**
FILTER lets you apply a WHERE condition to a specific aggregate without splitting the query into multiple subqueries. The result is multiple conditional aggregations in a single pass over the data.
Task**: Get total revenue, refunds, and failed transaction counts broken out by month — all in one row per month.
SELECT
DATE_TRUNC('month', created_at) AS month,
SUM(amount) FILTER (WHERE status = 'completed') AS revenue_completed,
SUM(amount) FILTER (WHERE status = 'refunded') AS revenue_refunded,
COUNT(*) FILTER (WHERE status = 'failed') AS failed_count
FROM transactions
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;Output:
month | revenue_completed | revenue_refunded | failed_count
------------------------+-------------------+------------------+--------------
2023-09-01 00:00:00+00 | 19.00 | | 0
2023-10-01 00:00:00+00 | 19.00 | | 0
2023-11-01 00:00:00+00 | 79.00 | | 0
2024-01-01 00:00:00+00 | 275.00 | | 0
2024-02-01 00:00:00+00 | 476.00 | 79.00 | 1
2024-03-01 00:00:00+00 | 555.00 | 79.00 | 0
2024-04-01 00:00:00+00 | 835.00 | 299.00 | 0
2024-05-01 00:00:00+00 | 775.00 | | 1
2024-06-01 00:00:00+00 | 598.00 | | 2
(9 rows)The alternative to FILTER is three separate subqueries joined together — more code, harder to read, and often slower. Note that SUM with FILTER returns NULL (not zero) when no rows match in a given month, which is accurate: there genuinely were no refunds in those months. Wrap in COALESCE(..., 0) if you prefer zeros.
FILTER is standard SQL and works in PostgreSQL and BigQuery. In Snowflake and some others, use SUM(CASE WHEN status = 'completed' THEN amount END) instead.
# 7. Detecting Consecutive Activity Streaks with Window Functions
**
Finding unbroken sequences — active months without a gap, consecutive days with transactions, subscription streaks — is one of the trickier SQL problems. The classic solution uses a window function to group rows into streaks without a recursive CTE.
The technique: assign each active month a sequential row number within its customer partition. If the months are truly consecutive, subtracting that row number from the month date produces the same constant value for every month in the streak. A gap breaks the constant.
Task**: Find each customer's consecutive active months (months with at least one completed transaction).
WITH monthly_activity AS (
SELECT
customer_id,
DATE_TRUNC('month', created_at)::DATE AS active_month
FROM transactions
WHERE status = 'completed'
GROUP BY customer_id, DATE_TRUNC('month', created_at)
),
with_prev AS (
SELECT
customer_id,
active_month,
LAG(active_month) OVER (
PARTITION BY customer_id
ORDER BY active_month
) AS prev_month
FROM monthly_activity
),
streak_groups AS (
SELECT
customer_id,
active_month,
SUM(CASE WHEN active_month = prev_month + INTERVAL '1 month' THEN 0 ELSE 1 END)
OVER (PARTITION BY customer_id ORDER BY active_month) AS streak_id
FROM with_prev
),
streaks AS (
SELECT
customer_id,
streak_id,
MIN(active_month) AS streak_start,
MAX(active_month) AS streak_end,
COUNT(*) AS streak_length_months
FROM streak_groups
GROUP BY customer_id, streak_id
)
SELECT customer_id, streak_start, streak_end, streak_length_months
FROM streaks
ORDER BY customer_id, streak_start;Output:
customer_id | streak_start | streak_end | streak_length_months
-------------+--------------+------------+----------------------
3317 | 2024-01-01 | 2024-01-01 | 1
3317 | 2024-03-01 | 2024-03-01 | 1
3317 | 2024-05-01 | 2024-05-01 | 1
4482 | 2023-09-01 | 2023-11-01 | 3
4482 | 2024-01-01 | 2024-01-01 | 1
4482 | 2024-03-01 | 2024-05-01 | 3
5901 | 2024-02-01 | 2024-06-01 | 5
6103 | 2024-01-01 | 2024-04-01 | 4
6103 | 2024-06-01 | 2024-06-01 | 1
6204 | 2024-01-01 | 2024-01-01 | 1
6204 | 2024-03-01 | 2024-03-01 | 1
6204 | 2024-05-01 | 2024-05-01 | 1
7891 | 2024-02-01 | 2024-02-01 | 1
7891 | 2024-04-01 | 2024-05-01 | 2
8810 | 2024-01-01 | 2024-02-01 | 2
8810 | 2024-04-01 | 2024-04-01 | 1
(16 rows)# Quick Reference
**
These patterns work in standard SQL without relying on database-specific features, and they appear frequently in analytical workflows such as retention analysis, upgrade funnel tracking, and revenue reporting.
Tip
When to Use It
LAG() / LEAD()
Time between events, before/after comparisons per entity
Self-join
Detect transitions between states (upgrades, re-activations)
ROW_NUMBER()
Top-N rows per group, deduplication
NTILE(n)
Customer segmentation into spend/activity tiers
Rolling window (ROWS BETWEEN)
Smooth noisy time-series, moving averages
FILTER
Multiple conditional aggregations in one query pass
Consecutive streak detection
Subscription streaks, retention analysis, session gaps
Once you're comfortable with them, many multi-step data transformations that are often handled in Python can be expressed more cleanly and efficiently in a single SQL query.
Bala Priya C** is a developer and technical writer from India. She likes working at the intersection of math, programming, data science, and content creation. Her areas of interest and expertise include DevOps, data science, and natural language processing. She enjoys reading, writing, coding, and coffee! Currently, she's working on learning and sharing her knowledge with the developer community by authoring tutorials, how-to guides, opinion pieces, and more. Bala also creates engaging resource overviews and coding tutorials.
関連記事
高度な結合技術：LATERAL 結合、セミ結合、アンチ結合
KDnuggets は、サブクエリが FROM クラースの先行列を参照できる LATERAL 結合や、一致する行のみを返すセミ結合、一致しない行を返すアンチ結合といった SQL の高度な結合技術について解説した。
クリックして再生する画像コンポーネント「click-to-play」の公開
Simon Willison が、クリックした際にのみ GIF を読み込む Web コンポーネント「click-to-play」を公開し、初期表示は静止画として動作する機能を導入した。
Pandas でループを書かない：試すべき 7 つの高速代替案
KDnuggets は、Pandas データ処理でループを使用する代わりに、ベクトル化や組み込み関数など 7 つの高速な代替手法を紹介している。
今日のまとめ
AI日報で今日の重要ニュースをまとめ読み