RATIO_TO_REPORT is a powerful Snowflake function that lets you calculate the ratio of a value to the various aggregations of data. The following query, for example, uses RATIO_TO_REPORT to calculate the Number of Sales, Amount of Sales and Average Sales as a Percentage for each Sales Associate
| TRANSACTION_ID |
AMOUNT |
ASSOCIATE |
| 1 |
100 |
Scott |
| 2 |
200 |
Scott |
| 3 |
20 |
Scott |
| 4 |
200 |
Angela |
| 5 |
200 |
Angela |
| 6 |
500 |
Bob |
RATIO_TO_REPORT query
select
sales_associate
, 100 * ratio_to_report(count(*)) over() as "NUMBER_OF_SALES_%"
, 100 * ratio_to_report(sum(amount)) over() as "AMOUNT_OF_SALES_%"
, 100 * ratio_to_report(avg(amount)) over() as "MEAN_OF_SALES_%"
from sales
group by sales_associate;
Output
| ASSOCIATE |
NUMBER_OF_SALES_% |
AMOUNT_OF_SALES_% |
| Scott |
50 |
26.2295 |
| Angela |
33.3333 |
32.7869 |
| Bob |
16.6667 |
40.9836 |