fashionly_new_customers_favorite_category

fashionly_new_customers_favorite_category.sql

WITH
  first_orders AS (
  SELECT
    o.order_id,
    o.user_id,
    ROW_NUMBER() OVER(PARTITION BY o.user_id ORDER BY o.created_at ASC) order_sequence
  FROM
    `bigquery-public-data.thelook_ecommerce.orders` o
  WHERE
    o.status NOT IN ('Cancelled',
      'Returned') QUALIFY order_sequence = 1 )
SELECT
  p.category,
  SUM(oi.sale_price) revenue,
  COUNT(DISTINCT fo.user_id) user_count
FROM
  first_orders fo
LEFT JOIN
  `bigquery-public-data.thelook_ecommerce.order_items` oi
ON
  oi.order_id = fo.order_id
LEFT JOIN
  `bigquery-public-data.thelook_ecommerce.products` p
ON
  p.id = oi.product_id
GROUP BY
  p.category
ORDER BY
  2 desc