Skip to content

Instantly share code, notes, and snippets.

@janbenetka

janbenetka/hist_grouping.sql

Created October 14, 2020 00:48
Show Gist options
  • Save janbenetka/2c0d2d1cb41767ab133afe323867aa4f to your computer and use it in GitHub Desktop.
Save janbenetka/2c0d2d1cb41767ab133afe323867aa4f to your computer and use it in GitHub Desktop.
Download ZIP
[Historgram grouping in BigQuery] #bigquery #sql
Raw
hist_grouping.sql
with count_per_identifier AS (
SELECT
identifier,
COUNT(DISTINCT home_area_id) home_count
FROM `uc-prox-core-dev.30_day_retention.home_identifier_weekly_8w_20d_100h`
GROUP BY identifier
)
SELECT home_count, SUM(count) count FROM (SELECT 1 as count, home_count FROM count_per_identifier)
GROUP BY home_count
ORDER BY home_count
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment