WITH product_metrics AS (
   SELECT
      p.item_id,
      p.item_name,
      p.item_status,
      p.category_id,
      pa.sale AS total_sales,
      pa.views AS total_views,
      pa.likes AS total_likes,
      pa.rating_star,
      pa.comment_count,
      CASE 
         WHEN pa.views > 0 THEN ROUND(SAFE_DIVIDE(pa.sale * 100.0, pa.views), 2)
         ELSE 0 
      END AS conversion_rate
   FROM
      `nekt_raw.shopee_products` p
      LEFT JOIN `nekt_raw.shopee_product_analytics` pa ON p.item_id = pa.item_id
   WHERE
      p.item_status = 'NORMAL'
)
SELECT
   item_id,
   item_name,
   total_sales,
   total_views,
   total_likes,
   rating_star,
   comment_count,
   conversion_rate
FROM
   product_metrics
ORDER BY
   total_sales DESC
LIMIT 50

WITH order_stats AS (
   SELECT
      order_status,
      COUNT(*) AS order_count,
      SUM(total_amount) AS total_revenue,
      AVG(total_amount) AS avg_order_value
   FROM
      `nekt_raw.shopee_orders`
   WHERE
      TIMESTAMP_SECONDS(create_time) >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
   GROUP BY
      order_status
),
total AS (
   SELECT SUM(order_count) AS total_orders FROM order_stats
)
SELECT
   os.order_status,
   os.order_count,
   ROUND(SAFE_DIVIDE(os.order_count * 100.0, t.total_orders), 2) AS percentage,
   ROUND(os.total_revenue, 2) AS total_revenue,
   ROUND(os.avg_order_value, 2) AS avg_order_value
FROM
   order_stats os, total t
ORDER BY
   os.order_count DESC

WITH campaign_metrics AS (
   SELECT
      apc.campaign_id,
      apc.common_info.ad_name AS campaign_name,
      apc.common_info.ad_type,
      apc.common_info.campaign_status,
      apc.common_info.campaign_placement,
      SUM(adp.impression) AS total_impressions,
      SUM(adp.clicks) AS total_clicks,
      SUM(adp.expense) AS total_spend,
      SUM(adp.broad_gmv) AS total_broad_gmv,
      SUM(adp.broad_order) AS total_broad_orders,
      SUM(adp.direct_gmv) AS total_direct_gmv,
      SUM(adp.direct_order) AS total_direct_orders
   FROM
      `nekt_raw.shopee_ads_product_campaigns` apc
      LEFT JOIN `nekt_raw.shopee_ads_daily_product_campaign_performance` adp 
         ON apc.campaign_id = adp.campaign_id
   WHERE
      DATE(adp.date) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
   GROUP BY
      apc.campaign_id,
      apc.common_info.ad_name,
      apc.common_info.ad_type,
      apc.common_info.campaign_status,
      apc.common_info.campaign_placement
)
SELECT
   campaign_name,
   ad_type,
   campaign_status,
   campaign_placement,
   total_impressions,
   total_clicks,
   ROUND(total_spend, 2) AS total_spend,
   ROUND(total_broad_gmv, 2) AS broad_gmv,
   total_broad_orders AS broad_orders,
   ROUND(total_direct_gmv, 2) AS direct_gmv,
   total_direct_orders AS direct_orders,
   ROUND(SAFE_DIVIDE(total_broad_gmv, total_spend), 2) AS broad_roas,
   ROUND(SAFE_DIVIDE(total_direct_gmv, total_spend), 2) AS direct_roas
FROM
   campaign_metrics
ORDER BY
   total_spend DESC