1

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

SQL Query:

    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;
  • Can you give us the output of SHOW CREATE TABLE my_table\G (or equivalent? What makes you think that your query needs further optimisation? – Vérace May 14 '18 at 7:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.