Predictive Hacks

How to Get the Percentage of Sum within Group in Snowflake

A very common request is to get the probability distribution function of a discrete variable, in other words, to get the percentage of each value over the total. Snowflake has a built-in function called ratio_to_report() for this report. Let’s provide some examples based on Snowflake’s documentation.

Let’s create a temporary table for exhibition purposes:

create temporary table store_profit (
    store_id integer, 
    province varchar,
    profit numeric(11, 2));
insert into store_profit (store_id, province, profit) values
    (1, 'Ontario', 300),
    (2, 'Saskatchewan', 250),
    (3, 'Ontario', 450),
    (4, 'Ontario', null)  -- hasn't opened yet, so no profit yet.
    ;
STORE_IDPROVINCEPROFIT
1Ontario300
2Saskatchewan250
3Ontario450
4OntarioNULL

Let’s get the percentage of profit using the ratio_to_report function.

select 
        store_id, profit, 
        ratio_to_report(profit) over () as percent_profit
    from store_profit
    order by store_id;
STORE_IDPROFITPERCENT_PROFIT
13000.3
22500.25
34500.45
4NULLNULL

Finally, let’s get the percentage of profit within each province generated by each store in that province:

select 
        province, store_id, profit, 
        ratio_to_report(profit) over (partition by province) as percent_profit
    from store_profit
    order by province, store_id;
PROVINCESTORE_IDPROFITPERCENT_PROFIT
Ontario13000.4
Ontario24500.6
Ontario4NULLNULL
Saskatchewan22501.0

Share This Post

Share on facebook
Share on linkedin
Share on twitter
Share on email

Subscribe To Our Newsletter

Get updates and learn from the best

More To Explore

Python

Image Captioning with HuggingFace

Image captioning with AI is a fascinating application of artificial intelligence (AI) that involves generating textual descriptions for images automatically.

Python

Intro to Chatbots with HuggingFace

In this tutorial, we will show you how to use the Transformers library from HuggingFace to build chatbot pipelines. Let’s