SELECT
RIC,
ROUND(SAFE_DIVIDE(SUM(Volume*Price),SUM(Volume)),3) AS VWAP,
COUNT(RIC) AS TradesCount,
SUM(Volume) AS TotalVolume,
ROUND(AVG(Volume),0) AS AvgTradeVolume,
MIN(Price) AS MinPrice,
MAX(Price) AS MaxPrice,
ROUND(SAFE_DIVIDE(100*(MAX(Price)-MIN(Price)),AVG(Price)),3) AS DeltaPricePercent,
ROUND(AVG(Price),3) AS AvgPrice
FROM
dbd-sdlc-prod.LSE_NORMALISED.LSE_NORMALISED
WHERE
RIC LIKE “%.L%”
AND (Date_Time BETWEEN TIMESTAMP(‘2025-09-04 00:00:00.000000’) AND
TIMESTAMP(‘2025-09-04 23:59:59.999999’))
AND Type=”Trade”
AND Volume >0
AND Price >0
AND NOT (Qualifiers LIKE ‘Off Book Trades%’)
AND NOT (Qualifiers LIKE ‘%Previous Day Trade%’)
AND NOT (Qualifiers LIKE ‘%CLS%’)
AND NOT (Qualifiers LIKE ‘U[ACT_FLAG1];U[CONDCODE_1]%’)
AND NOT (Qualifiers LIKE ’46—A—P—-[MMT_CLASS]’)
AND NOT (Qualifiers LIKE ’46-1-A—P—-[MMT_CLASS]’)
AND NOT (Qualifiers LIKE ’47—A—J—-[MMT_CLASS]’)
AND NOT (Qualifiers LIKE ’47—A—P—-[MMT_CLASS]’)
AND NOT (Qualifiers LIKE ‘%Auction%’)
GROUP BY
RIC
ORDER BY
TotalVolume DESC
