APPROX_COUNT_DISTINCT

1
SELECT
2
  APPROX_COUNT_DISTINCT(user_id) as approx_unique_users,
3
  COUNT(DISTINCT user_id) as exact_unique_users
4
FROM `project.dataset.page_views`
5
WHERE DATE(timestamp) = '2024-06-25';

1
SELECT
2
  DATE(timestamp) as date,
3
  APPROX_COUNT_DISTINCT(user_id) as daily_unique_users
4
FROM `project.dataset.events`
5
WHERE timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
6
GROUP BY date
7
ORDER BY date DESC;

1
SELECT
2
  country,
3
  APPROX_COUNT_DISTINCT(user_id) as unique_users,
4
  APPROX_COUNT_DISTINCT(session_id) as unique_sessions,
5
  APPROX_COUNT_DISTINCT(device_id) as unique_devices
6
FROM `project.dataset.analytics`
7
GROUP BY country
8
ORDER BY unique_users DESC
9
LIMIT 10;

1
-- This query can be 10-100x faster than COUNT(DISTINCT)
2
-- for tables with billions of rows
3
SELECT
4
  APPROX_COUNT_DISTINCT(customer_id) as unique_customers,
5
  APPROX_COUNT_DISTINCT(product_id) as unique_products,
6
  APPROX_COUNT_DISTINCT(CONCAT(customer_id, '-', product_id)) as unique_pairs
7
FROM `project.dataset.transactions`
8
WHERE EXTRACT(YEAR FROM transaction_date) = 2024;