[SQL/BigQuery] 신규 vs 기존 유저의 구매

특정 월(예: 2025-07)에 신규 유저(가입월=해당월) vs 기존 유저의 구매 금액 합계를 비교하라.

 

• users 테이블의 created_at이 가입일
• order_items 테이블의 created_at이 주문일

WITH users_type AS(
  SELECT 
    id AS user_id,
    DATE(created_at) AS join_date,
    CASE
      WHEN DATE_TRUNC(DATE(created_at),MONTH) = '2025-07-01' THEN 'new'
      WHEN DATE_TRUNC(DATE(created_at),MONTH) < '2025-07-01' THEN 'old'
      ELSE 'other' 
    END AS type
  FROM `bigquery-public-data.thelook_ecommerce.users`
)
SELECT 
  t.type,
  IFNULL(COUNT(i.user_id),0) AS user_size,
  IFNULL(SUM(i.sale_price),0) AS total_sales
FROM `bigquery-public-data.thelook_ecommerce.order_items` i
JOIN users_type t ON i.user_id = t.user_id
WHERE DATE_TRUNC(DATE(i.created_at),MONTH) = '2025-07-01'
GROUP BY t.type;