CODEWITHSUNDEEP
sqlgoogle-bigquery
Assi Guri
Assi Guri

Reputation: 57

How to find for each of the top 3 returned value from one column, the 3 top values from another column?

I want to return for each of the 3 most common values in one column, the 3 most common values from a different column.

I've managed to return the 3 most common values from the first column:

SELECT vehicle_type_code1 as vehicle_type
FROM `bigquery-public-data.new_york.nypd_mv_collisions`
GROUP BY vehicle_type
ORDER BY count(vehicle_type_code1) desc
LIMIT 3

But now I want to find for each of them, the most common 'cross_street_name'. I can do it manually and group by 'cross_street_name' where 'vehicle_type_code1' is equal to each of them, but I guess there is a better way.

Upvotes: 0

Views: 61

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172964

Consider below approach

select vehicle_type, 
  string_agg(cross_street_name, ', ' order by cnt desc limit 5) as cross_streets
from (
  select vehicle_type_code1 as vehicle_type, cross_street_name, count(*) as cnt 
  from `bigquery-public-data.new_york.nypd_mv_collisions`
  where cross_street_name != ''
  and vehicle_type_code1 != ''
  group by vehicle_type, cross_street_name
)
group by vehicle_type
order by sum(cnt) desc
limit 5

with output

enter image description here

Upvotes: 1

Related Questions