Refund Logic

SELECT
  DATE_TRUNC(DATE(order_processed_at_local_datetime), WEEK(MONDAY)) AS week_start,
  SUM(order_gross_revenue) AS gross_revenue,
  SUM(order_refunds) AS refunds,
  SAFE_DIVIDE(ABS(SUM(order_refunds)), NULLIF(SUM(order_gross_revenue), 0)) AS refund_rate
FROM `your_project.sm_transformed_v2.obt_orders`
WHERE is_order_sm_valid = TRUE
  AND order_processed_at_local_datetime >= DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 90 DAY)
GROUP BY 1
ORDER BY week_start DESC;

SELECT
  sm_order_key,
  order_id,
  order_gross_revenue,
  order_refunds
FROM `your_project.sm_transformed_v2.obt_orders`
WHERE is_order_sm_valid = TRUE
  AND ABS(order_refunds) > order_gross_revenue
ORDER BY ABS(order_refunds) DESC
LIMIT 200;