how to get % of transaction amount from top 1000 sellers over all sellers, Month to Date, using bigquery

user_10543022 0
Viewed 53

so I have table that consist : table, shop_id, and transaction amount.

date_key    shop_id    transaction_amount
2022-01-01    S_001       2000
2022-01-01    S_002       2500
2022-01-02    S_001       2600
2022-01-02    S_002       2200
.
.
.
.

the goal is I want to calculate the % of transaction amount of top 1000 sellers MTD. on average I have around 50k sellers that has transaction on each day. since the calculation is month to date, so there's possibility if we will find different top 1000 sellers on each day.

top 1000 seller mtd meaning : the performance is based on cumulative MTD transaction amount e.g for data on 2022-01-05 the we need to calculate cumulative transaction amount from 2022-01-01 to 2022-01-04 for each shop_id then sort it based on highest transaction amount.

the goal is to create table below

date_key   amount_from_top_1000_sellers(a) amount_from_all_sellers (b)    ratio (a/b)
2022-01-01         4000                              200000                   2%
...
...
...
google-bigquery sql
1 Answers

Window Function will do this task. For each day you need a row_number ordered by the selling amount column.

First we generate some random data in the tbl table. Then we add the row number for each day. Next we need to filter the first 100 top seller per day (if statement). The increase to 1000 top seller per day is obvious. The calculation of remaining selling amount and the ration can be done by including the last SELECT in the WITH as further table and query this table again with SELECT.

WITH
  tbl AS (
  SELECT
    DATE_SUB(CURRENT_DATE(),INTERVAL d day) AS date_key,
    a AS shop_id,
    100*RAND() AS transaction_amount
  FROM
    UNNEST(GENERATE_ARRAY(1,1000)) a,
    UNNEST(GENERATE_ARRAY(0,100)) d ),
  trunc_to_month AS (
  Select
  date_trunc(date_key,month) as date_key_month,#aggregate to month and year
  shop_id,
  sum(transaction_amount) as transaction_amount,
  from tbl
  group by 1,2
  ),
  tmp AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY date_key_month ORDER BY transaction_amount DESC ) AS rownum
  FROM
    trunc_to_month )
SELECT
  date_key_month, # keep only the month and year; remove the day 
  SUM(transaction_amount) AS transaction_amount_total,
  SUM(IF(rownum<=100,transaction_amount,0)) AS amount_from_top_100_sellers
FROM
  tmp
GROUP BY 1
user_165295760
Related