100% bar chart

Explore this snippet with some demo data here.

Description

100% bar charts compare the proportions of a metric across different groups. To build one you'll need:

A numerical column as the metric you want to compare. This should be represented as a percentage.
2 categorical columns. One for the x-axis and one for the coloring of the bars.

SELECT 
   AGG_FN(<COLUMN>) as metric,
   <XAXIS_COLUMN> as x,
   <COLOR_COLUMN> as color,
FROM 
   <TABLE>
GROUP BY
   x, color

where:

AGG_FN is an aggregation function like SUM, AVG, COUNT, MAX, etc.
COLUMN is 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.
XAXIS_COLUMN is the group you want to show on the x-axis. Make sure this is a date, datetime, timestamp, or time column (not a number)
COLOR_COLUMN is the group you want to show as different colors of your bars.

In this example with some London weather data, we calculate the percentage of days in each month that are of each weather type (e.g. rain, sun).

-- a
select
  count(DATE) days,
  month(date) month, 
  WEATHER 
from PUBLIC.LONDON_WEATHER
group by month, WEATHER
order by month

select
  count(DATE) days,
  month(date) month
from PUBLIC.LONDON_WEATHER
group by month
order by month

-- c
select
  "a".WEATHER,
  "a".MONTH,
  "a".DAYS / "b".DAYS per_days
from
  "a" left join "b" on "a".MONTH = "b".MONTH