Recursive CTE to List Years Without Gaps

If I have a requirement to list the years from a start year incrementing to the current year without any gap years, I can write the following simple SELECT statement within a CTE.

WITH FINANCIAL_YEARS AS (
    SELECT 2020 AS FINANCIAL_YEAR
    UNION SELECT 2021
    UNION SELECT 2022
    UNION SELECT 2023  
)

However its pretty easy to see how tedious this can be especially when trying to maintain it when a new year ticks over. I found this neat trick using recursive CTEs to avoid gaps in dates and tweaked it to deal with year increments:

WITH FINANCIAL_YEARS AS (
    SELECT '2020-01-01'::DATE AS YEAR_DATE
    UNION ALL
    SELECT DATEADD(YEAR, 1, YEAR_DATE) AS YEAR_DATE
    FROM FINANCIAL_YEARS
    WHERE YEAR_DATE < DATEADD(YEAR, -1, DATEADD(MONTH, 6, CURRENT_DATE())
)
SELECT DATE_PART(YEAR, YEAR_DATE) AS FINANCIAL_YEAR FROM FINANCIAL_YEARS

Its even simpler if you only want the year component or any other kind of numeric increment from a starting number:

WITH FINANCIAL_YEARS AS (
    SELECT 2020 AS FINANCIAL_YEAR
    UNION ALL
    SELECT FINANCIAL_YEAR + 1 AS FINANCIAL_YEAR
    FROM FINANCIAL_YEARS
    WHERE FINANCIAL_YEAR < YEAR(DATEADD(MONTH, 6, CURRENT_DATE()))
)
SELECT FINANCIAL_YEAR FROM FINANCIAL_YEARS

Reporting on your After Hours in SnowFlake

My current work at Tyro is predominantly in the Snowflake Data Warehouse creating reports, building queries, and maintaining the DDL.

As such it is necessary to keep monitoring my constant usage as the team starts to grow and engineers become more accountable.

One good technique is to query the built-in view:

SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY

It consists of the USER_NAME, ROLE_NAME, and even the QUERY_TEXT of query that was run in snowflake.

What we need in order to find out the number of after hours worked on a day to day basis is the START_TIME of the query that was executed. With that we can then filter by any queries run after 5PM (i.e. the 17th hour of the day), and the difference between max and min dates for each day to find out how many hours were worked after that time.

SELECT
    USER_NAME,
    ROUND(TIMESTAMPDIFF('MINUTE', MIN(START_TIME), MAX(START_TIME)) / 60, 2) AFTER_HOURS,
    DAYNAME(MIN(START_TIME)) DAY,
    MIN(START_TIME) START_DATE,
    MAX(START_TIME) END_DATE
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE USER_NAME = '<user_name>'
    AND DATE_PART('HOUR', START_TIME) > 17
GROUP BY
    USER_NAME,
    DATE_PART('DAY', START_TIME),
    DATE_PART('MONTH', START_TIME),
    DATE_PART('YEAR', START_TIME)
ORDER BY MIN(START_TIME) DESC;