Using GoogleSQL I want to extract just the first 24 records of each record_id, based on time period. After that perform an average over that range (1-24), and also calculate the growth rate period over period. Query works fine but looking for optimization.
record_id, period, downloads 123, 2004-01-02, 100 123, 2004-01-09, 200 345, 2004-01-09, 180 123, 2004-01-16, 110 345, 2004-01-16, 200 345, 2004-01-23, 500 123, 2004-01-23, 100
SELECT id, period, record_id, downloads, AVG(downloads) OVER (PARTITION BY record_id) AS avg_downloads, 100 * (downloads - growth) / growth AS growth_rate_percent, -- (Present - Past) / Past AVG(growth_rate_percent) OVER (PARTITION BY record_id) AS avg_growth_rate_percent FROM (SELECT record_id, period, downloads AS downloads, ROW_NUMBER() OVER (PARTITION BY record_id ORDER BY period) AS id, -- Enumerate Rows. LAG(downloads) OVER (PARTITION BY record_id ORDER BY period) AS growth, -- Previous Record. AVG(downloads) OVER (PARTITION BY record_id) AS avg_all_downloads, -- Average downloads. FROM my_table) WHERE id <= 24;