kentoadded-olive
    Updated 2024-10-26
    SELECT
    TO_CHAR(TO_DATE(BLOCK_TIMESTAMP_HOUR), 'DD') ||
    CASE
    WHEN TO_CHAR(TO_DATE(BLOCK_TIMESTAMP_HOUR), 'DD') IN ('01', '21', '31') THEN 'st'
    WHEN TO_CHAR(TO_DATE(BLOCK_TIMESTAMP_HOUR), 'DD') IN ('02', '22') THEN 'nd'
    WHEN TO_CHAR(TO_DATE(BLOCK_TIMESTAMP_HOUR), 'DD') IN ('03', '23') THEN 'rd'
    ELSE 'th'
    END AS day,
    SUM(UNIQUE_FROM_COUNT) AS unique_senders
    FROM cosmos.stats.ez_core_metrics_hourly
    WHERE EXTRACT(MONTH FROM BLOCK_TIMESTAMP_HOUR) = 9
    AND EXTRACT(YEAR FROM BLOCK_TIMESTAMP_HOUR) = EXTRACT(YEAR FROM CURRENT_DATE)
    GROUP BY TO_DATE(BLOCK_TIMESTAMP_HOUR)
    ORDER BY TO_DATE(BLOCK_TIMESTAMP_HOUR);

    QueryRunArchived: QueryRun has been archived