# Query 4

This is a portfolio aggregation query that uses Common Table Expression (CTE), JOIN, and window functions. It also computes min, max, standard deviation, weighted average, and percentiles for each company stock.

```sql
WITH folio AS (
  SELECT id, stock_symbol, shares, share_price, trade_time
  FROM trade
),
AggCalcs AS (
  SELECT
    stock_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 Average
    SUM(share_price*shares) AS total_pvalue
  FROM trade
  GROUP BY 1
)
SELECT
  DISTINCT 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 Q3
FROM folio
JOIN AggCalcs ON (folio.stock_symbol = ACsymb)
ORDER BY folio.stock_symbol;

```

***

Modified at: March 2, 2021

Source: [/cloud/query-data/advanced-query-topics/example-building-a-sample-stock-trade-database/query-4/](https://docs.singlestore.com/cloud/query-data/advanced-query-topics/example-building-a-sample-stock-trade-database/query-4/)

(An index of the documentation is available at /llms.txt)