このSQLには不具合が含まれています。見つけ出して欲しいです。

こんにちは、YOUTRUST のやまでぃ（YOUTRUST/X）です。

最近のわたくしごとですが

最近重たすぎる腰を上げてダイエットに取り組んでいます。

筋トレやら有酸素運動やら16時間断食など色々薄く広くやってみていますが、炭水化物制限が一番効果がある気がしています。

炭水化物と脂質を控え目にし、タンパク質や野菜、魚、フルーツの摂取を意識していること以外は、普通に満足するまで量を食べます。お酒も主にハイボールですがまぁまぁな量を飲みます。

3週間くらい前に「やるぞ！」と気持ちを新たにし、それから毎日100gずつ減量しています。つまり2kg減です。

日本医師会によると、自分の適正体重は70.49kgらしいので、71kgくらいまでは減量してみようかなと思います。

このペースが保てれば年内には達成できそうです。

ただ、シンプルに細くなるだけなのは嫌なので、筋トレを継続して一定の筋肉量は保つよう気を付けていきます。

今回は何の話？

先日発見した分析用テーブル作成のSQLの不具合についてです。

弊社のデータ基盤では dbt を利用して分析用テーブル作成のSQLを管理していて、つい最近まで全体の実行時間がおよそ10時間(!?)にも及んでおり、コスト面・運用面で問題があってSQLの最適化が急務となっていました。

一つ一つのテーブルの実行履歴やSQL文を眺め、地道に改善していく日々が続き、最近ようやく3時間を切るくらいに改善することができました。

今回はそんな中で発見されたSQLの不具合について紹介します。

問題、以下のSQLには不具合が含まれています。

こちらのSQLの目的は、「検索経由のスカウト数」を集計することです。（実際のSQLを簡素化して記載しています。）

リクルーターの方がユーザー検索機能を利用し、結果画面から気になるユーザーのプロフィール画面へアクセス（≒足跡が残る）し、そのままスカウトを送信したケースを数値で追うためのものです。

SELECT
    contract.contract_id
    , contract.recruiter_team_id
    , contract.recruiter_team_name
    , COUNT(DISTINCT scout.scout_room_id) AS scout_from_search -- 検索経由でのスカウト数
    , COUNT(DISTINCT CASE WHEN scout.is_replied THEN scout.scout_room_id ELSE NULL END) AS scout_reply_from_search -- 検索経由でのスカウト返信数
FROM
    {{ ref('user_search') }} AS search -- 検索ログ
INNER JOIN
    {{ ref('user_footprint') }} AS footprint -- 足跡ログ
    ON
        search.user_id = footprint.visitor_user_id
        AND TIMESTAMP_DIFF(footprint.created_at, search.created_at, HOUR) BETWEEN 0 AND 5 -- 検索→足跡まで6時間以内
INNER JOIN
    {{ ref('scout_room') }} AS scout -- スカウトルーム
    ON
        search.recruiter_team_id = scout.recruiter_team_id
        AND footprint.user_id = scout.scouted_user_id
        AND footprint.created_at < scout.scouted_at -- 足跡日時 < スカウト日時
INNER JOIN
    {{ ref('recruiter_team_contract') }} AS contract -- リクルーター契約
    ON
        search.recruiter_team_id = contract.recruiter_team_id
WHERE
    scout.scouted_at BETWEEN contract.started_at AND contract.ended_at
GROUP BY
    1, 2, 3

解答編

上記SQLを日本語に翻訳しながら読み解いていきます。

まずは下記の部分。

FROM
    {{ ref('user_search') }} AS search -- 検索ログ
INNER JOIN
    {{ ref('user_footprint') }} AS footprint -- 足跡ログ
    ON
        search.user_id = footprint.visitor_user_id
        AND TIMESTAMP_DIFF(footprint.created_at, search.created_at, HOUR) BETWEEN 0 AND 5 -- 検索→足跡まで6時間以内

こちらを日本語に翻訳すると、「検索ログに対し、検索実行から6時間以内に検索ユーザーが残した足跡ログをJOIN」になります。

具体的には、下記のように一回の検索ログに対し、複数の足跡ログがJOINされるようなイメージです。

検索実行日時	足跡の相手ユーザーID	足跡作成日時
2024/09/12 10:00:00	1000	2024/09/12 10:01:00
2024/09/12 10:00:00	1001	2024/09/12 10:02:30
2024/09/12 10:00:00	1002	2024/09/12 10:04:10

続いて下記の部分を翻訳します。

INNER JOIN
    {{ ref('scout_room') }} AS scout -- スカウトルーム
    ON
        search.recruiter_team_id = scout.recruiter_team_id
        AND footprint.user_id = scout.scouted_user_id
        AND footprint.created_at < scout.scouted_at -- 足跡日時 < スカウト日時

こちらを日本語に翻訳すると、「検索→足跡ログに対し、検索ユーザーの所属するリクルーターチームのリクルーターから、足跡先のユーザーへ、足跡以降に送信されたスカウトログをJOIN」になります。

具体的には下記のようなイメージです。

検索実行日時	足跡の相手ユーザーID	足跡作成日時	スカウト日時
2024/09/12 10:00:00	1000	2024/09/12 10:01:00	2024/09/12 10:02:00
2024/09/12 10:00:00	1001	2024/09/12 10:02:30	2024/09/12 10:03:00

（ユーザーID 1002 にはスカウト送信していないので、結果行には含まれていません。）

最後に下記の部分を翻訳します。

INNER JOIN
    {{ ref('recruiter_team_contract') }} AS contract -- リクルーター契約
    ON
        search.recruiter_team_id = contract.recruiter_team_id
WHERE
    scout.scouted_at BETWEEN contract.started_at AND contract.ended_at

こちらを日本語に翻訳すると、「スカウト日時における、検索ユーザーが所属するリクルーターチームの契約情報をJOIN」になります。

これは結果表示用ですね。主にリクルーターチーム名（「株式会社YOUTRUST」等）カラムを取得しています。

まとめると、