SQL練習場上級 2026年5月11日約2分で読めます #アトリビューション #SQL練習問題 #広告効果測定

SQL練習問題：広告クリック後7日以内の購入を集計する

難易度: 上級 / 使用テーブル: ad_clicks, campaigns, orders

問題

ad_clicks、campaigns、ordersを使い、広告クリック日から7日以内に発生した注文をキャンペーン別に集計してください。

スターターSQL

SELECT
  c.name,
  COUNT(a.id) AS clicks,
  SUM(a.cost) AS cost
FROM ad_clicks a
JOIN campaigns c ON a.campaign_id = c.id
GROUP BY c.id, c.name
ORDER BY cost DESC

解答例

SELECT
  c.name,
  c.channel,
  COUNT(DISTINCT a.id) AS clicks,
  SUM(a.cost) AS cost,
  COUNT(DISTINCT o.id) AS attributed_orders,
  SUM(o.total_amount) AS attributed_revenue,
  ROUND(SUM(o.total_amount) * 1.0 / NULLIF(SUM(a.cost), 0), 2) AS attributed_roas
FROM ad_clicks a
JOIN campaigns c ON a.campaign_id = c.id
LEFT JOIN orders o
  ON a.user_id = o.user_id
 AND julianday(o.order_date) BETWEEN julianday(a.click_date) AND julianday(a.click_date) + 7
GROUP BY c.id, c.name, c.channel
ORDER BY attributed_roas DESC

見るべきポイント

広告接触と購入を期間条件付きでJOINする練習です。
実務ではアトリビューション期間、重複クリック、媒体IDと自社DB IDの対応が論点になります。

SQL練習場でこの問題を試す

関連