Group RUM data optionally by generation

[trieloff]

naive generation grouping

WITH by_generation AS (
SELECT 
    id,
    generation,
    IF(url IN (
        "https://www.adobe.com/express/", 
        "https://www.adobe.com/express/feature/image/remove-background",
        "https://www.adobe.com/express/create/flyer",
        "https://www.adobe.com/express/create/banner",
        "https://www.adobe.com/express/discover/messages/card/birthday/mother",
        "https://www.adobe.com/express/thisandthat2"), url, "other") AS url,
    MAX(weight) AS weight,
    MAX(time) AS last,
    MIN(time) AS first
FROM `helix-225321.helix_rum.rum202109` 
WHERE 
    generation IS NOT NULL
    AND CAST(time AS STRING) > CAST(UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 12 HOUR)) AS STRING)
GROUP BY id, generation, url),
results AS (
SELECT 
    SUM(weight) AS pageviews,
    IF((MAX(last) - MIN(first)) / 1000 / 3600 > 0, ROUND(SUM(weight) / ((MAX(last) - MIN(first)) / 1000 / 3600)), 0) AS pageviews_per_hour,
    (MAX(last) - MIN(first)) / 1000 / 3600 AS hours,
    url,
    generation
FROM by_generation
GROUP BY generation, url
ORDER BY pageviews_per_hour
)

SELECT * FROM results