Dynamic Window/Analytic Functions
Sometimes a static window function is not enough. In this (dialect agnostic) example, the columns you want to sum depend on the columns selected in the outside select. Instead of partition_by like the previous example, this one uses partition_by_including.
Select the columns:
bipp generates the SQL:
SELECT t000.country AS _0, avg(t001.transaction_amount2) AS _1 FROM `online_retail`.`transactions` AS t000 JOIN ( SELECT transactions.invoice_num AS invoice_num, transactions.country AS country, sum(transactions.invoice_qnty * transactions.unit_price) AS transaction_amount2 FROM `online_retail`.`transactions` AS transactions GROUP BY invoice_num, country) AS t001 ON t000.invoice_num=t001.invoice_num AND t000.country=t001.country GROUP BY 1
Fetch the data:
IN THIS PAGE