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),
(3, 'Ontario', 450),
(4, 'Ontario', null)  -- hasn't opened yet, so no profit yet.
;

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;

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;

### Get updates and learn from the best

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