Explore this snippet with some demo data here.
Heatmaps are a great way to visualize the relationship between two variables enumerated across all possible combinations of each variable. To create one, your data must contain
- 2 categorical columns (e.g. not numeric columns)
- 1 numerical column
SELECT AGG_FN(<COLUMN>) as metric, <CAT_COLUMN1> as cat1, <CAT_COLUMN2> as cag2, FROM <PROJECT.SCHEMA.TABLE> GROUP BY cat1, cat2
AGG_FNis an aggregation function like
COLUMNis the column you want to aggregate to get your metric. Make sure this is a numeric column. Must add up to 1 for each bar.
CAT_COLUMN2are the groups you want to compare. One will go on the x axis and the other on the y-axis. Make sure these are categorical or temporal columns and not numerical fields.
In this example with some Spotify data, we’ll see the average daily streams across different days of the week and months of the year.
-- a select sum(streams) daily_streams, day from spotify.spotify_daily_tracks group by day
select avg(a.daily_streams) avg_daily_streams, format_date('%m - %b',day) month, format_date('%w %A',day) day_of_week from a group by day_of_week, month