COUNTIF

1
SELECT
2
  COUNTIF(status = 'completed') as completed_orders,
3
  COUNTIF(status = 'pending') as pending_orders,
4
  COUNTIF(status = 'cancelled') as cancelled_orders,
5
  COUNT(*) as total_orders
6
FROM `project.dataset.orders`;

1
SELECT
2
  product_category,
3
  COUNTIF(price > 1000000) as premium_products,
4
  COUNTIF(price BETWEEN 500000 AND 1000000) as mid_range,
5
  COUNTIF(price < 500000) as budget_products
6
FROM `project.dataset.products`
7
GROUP BY product_category;

1
SELECT
2
  DATE(timestamp) as date,
3
  COUNT(*) as total_visits,
4
  COUNTIF(converted = TRUE) as conversions,
5
  ROUND(COUNTIF(converted = TRUE) * 100.0 / COUNT(*), 2) as conversion_rate
6
FROM `project.dataset.user_sessions`
7
GROUP BY date
8
ORDER BY date DESC
9
LIMIT 7;

1
SELECT
2
  customer_id,
3
  COUNTIF(order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)) as orders_last_30_days,
4
  COUNTIF(order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)) as orders_last_90_days,
5
  COUNT(*) as total_orders
6
FROM `project.dataset.orders`
7
GROUP BY customer_id
8
HAVING COUNTIF(order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)) > 0
9
LIMIT 10;