Query 4
Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
This is a portfolio aggregation query that uses Common Table Expression (CTE), JOIN, and window functions.
WITH folio AS (SELECT id, stock_symbol, shares, share_price, trade_timeFROM trade),AggCalcs AS (SELECTstock_symbol AS ACsymb,MAX(share_price) AS pmax,MIN(share_price) AS pmin,STD(share_price) AS pstd,SUM(share_price*shares)/SUM(shares) AS avg_pps, ## Weighted AverageSUM(share_price*shares) AS total_pvalueFROM tradeGROUP BY 1)SELECTDISTINCT folio.stock_symbol,avg_pps,pmin,pmax,percentile_cont(.25) WITHIN group (ORDER BY share_price) OVER (PARTITION BY stock_symbol) AS Q1,percentile_cont(.5) WITHIN group (ORDER BY share_price) OVER (PARTITION BY stock_symbol) AS median,percentile_cont(.75) WITHIN group (ORDER BY share_price) OVER (PARTITION BY stock_symbol) AS Q3FROM folioJOIN AggCalcs ON (folio.stock_symbol = ACsymb)ORDER BY folio.stock_symbol;
Last modified: March 2, 2021