sql/ratio_to_report.sql at main · saqib-ali/sql · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
create or replace table scratch.saqib_ali.TRANSACTIONS (order_id number, amount number, transaction_date date, channel varchar(20));
insert into scratch.saqib_ali.TRANSACTIONS values (1, 10, '2022-12-01', 'store');
insert into scratch.saqib_ali.TRANSACTIONS values (2, 20, '2022-12-01', 'online');
insert into scratch.saqib_ali.TRANSACTIONS values (3, 20, '2022-12-01', 'store');
insert into scratch.saqib_ali.TRANSACTIONS values (4, 20, '2022-12-02', 'store');
insert into scratch.saqib_ali.TRANSACTIONS values (5, 30, '2022-12-02', 'store');
insert into scratch.saqib_ali.TRANSACTIONS values (6, 30, '2022-12-02', 'store');
insert into scratch.saqib_ali.TRANSACTIONS values (7, 20, '2022-12-02', 'store');

select * from scratch.saqib_ali.TRANSACTIONS;


-- With RATIO_TO_REPORT
select
  transaction_date
  , channel
  , sum(amount) as total_sales
  , ratio_to_report(total_sales) over (partition by transaction_date)
    as channel_percentage
from scratch.saqib_ali.TRANSACTIONS
group by transaction_date, channel;


-- VS.

-- Without RATIO_TO_REPORT
select
  transaction_date
  , channel
  , sum(amount) as total_by_channel
  , sum(total_by_channel) over (partition by transaction_date) as total_online_and_store
  , ((total_by_channel / total_online_and_store) * 100)::NUMBER || '%' as channel_percentage
from scratch.saqib_ali.TRANSACTIONS
group by transaction_date, channel;