CODEWITHSUNDEEP
sqlgoogle-bigquery
Jane
Jane

Reputation: 61

Get a histogram of a query Bigquery

The question is: Show customer's transaction distribution for completed RIDE orders between 1st - 10th of April 2018 (Distribution of customers that have done 1 transaction, 2, 3,4,etc)

And the preview of table that's querying is:

enter image description here

My query is:

SELECT customer_no, COUNT(*) AS total_transaction FROM [bi-dwhdev-01:source.daily_order]
WHERE DATE(order_time) >=  '2018-04-01'AND DATE(order_time) <=  '2018-04-10'
GROUP BY customer_no
ORDER BY total_transaction DESC;

I'm wondering how to get a distribution in Bigquery(either Legacy or Standard)? Thanks in advance!

Upvotes: 2

Views: 1767

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

I think you want two levels of aggregation:

SELECT total_transaction, COUNT(*)
FROM (SELECT customer_no, COUNT(*) AS total_transaction
      FROM [bi-dwhdev-01:source.daily_order]
      WHERE DATE(order_time) >=  '2018-04-01' AND DATE(order_time) <=  '2018-04-10'
      GROUP BY customer_no
     ) c
GROUP BY total_transaction
ORDER BY total_transaction DESC;

Upvotes: 1

Related Questions