SQL tips

sql_BigQuery_date_tips.sql

-- astuces SQL BigQuery sur les date & null
select 
  -- coalesce == ifnull
  coalesce(null, null, 'toto') demo_coalesce,
  ifnull(null, 'toto') demo_ifnull,
  nullif('', '') demo_nullif, -- null if given variable is a given value
  -- dt_from_dt
  safe_cast('2025-04-30' as date) dt_from_dt, -- ok
  safe_cast('' as date) dt_from_dt, -- null
  safe_cast(null as date) dt_from_dt, -- null
  safe_cast('pipo' as date) dt_from_dt, -- null
  -- dt_from_dth
  safe_cast(left('2025-04-30 01:01:00', 10) as date) dt_from_dth, -- ok
  safe_cast('2025-04-30 01:01:00' as date) dt_from_dth, -- null
  -- dth_from_dth
  safe_cast('2025-04-30 01:01:00' as datetime) dth_from_dth, -- ok
  safe_cast('2025-04-30 01:01' as datetime) dth_from_dth, -- null
  safe_cast(rpad('2025-01-01 01', 19, ':00') as datetime) dth_from_dth, -- ok
  -- demo date
  current_date() aujdhui,
  date_trunc(current_date(), week) dt_week,
  date_trunc(current_date(), week(sunday)) dt_week_dimanche,
  date_trunc(current_date(), week(monday)) dt_week_lundi,

sql_snowflake_dimDate_generator.sql

-- compute date range
create or replace temp table date_diff as
select
    '2020-01-01'::date as dt_debut,
    date_trunc('year', current_date) + interval '1 YEAR' as dt_fin, -- 1er janvier Y+1
    datediff('day', dt_debut, dt_fin) as days; --> 31 déc current year

-- let's go :)
WITH calendar AS (
    SELECT DATEADD(DAY, SEQ4(), '2020-01-01')::date AS dt
    FROM TABLE(GENERATOR(ROWCOUNT=>(SELECT MAX(days) FROM date_diff)))  
)
SELECT
    dt,
    year(dt) AS year,
    quarter(dt) AS quarter,
    month(dt) AS month,
    day(dt) AS day,
    week(dt) AS week,
    dayofweek(dt) day_of_week,
    dayname(dt) AS day_name,
    iff(day_of_week IN (1,7), true, false) as is_weekend,
    iff(day_of_week BETWEEN 2 AND 6, true, false) as is_business_day,
FROM calendar;

-- inspirations
-- https://www.zuar.com/blog/date-dimensions-date-scaffold-date-spine-snowflake/
-- https://github.com/jacobbaruch/calendar-udf-snowflake/blob/main/sql/03_generate_calendar.sql

create temp table date_dummy_1 as
select datediff('day', '2020-01-01', current_date) as days;

SELECT DATEADD(DAY, SEQ4(), '2020-01-01')::date AS MY_DATE
FROM TABLE(GENERATOR(ROWCOUNT=>(SELECT MAX(days) FROM date_dummy_1)));

sql_top_players.sql

--------------------------------------------------------------------
-- 🦆 DuckDB - Top player -> 🥇🥈🥉 row_number, rank & dense_rank
--------------------------------------------------------------------
with players(player, score) as (
  values
	('Grégoire', 20), ('Corentin', 18), ('Antoine', 30),
	('bob', 12), ('kevin', 12), ('dylan', 12)
)
select
	*,
	row_number() over(order by score) rg_rownumber,
	rank() over(order by score) rg_rank,
	dense_rank() over(order by score) rg_denserank,
from players;


-----------------------------------------------------------------------
-- ❄️ Snowflake - Top player -> 🥇🥈🥉 row_number, rank & dense_rank
-----------------------------------------------------------------------
with players(player, score) as (
  select * from (values
	('Grégoire', 20), ('Corentin', 18), ('Antoine', 30),
	('bob', 12), ('kevin', 12), ('dylan', 12)
  )
)
select
	*,
	row_number() over(order by score) rg_rownumber,
	rank() over(order by score) rg_rank,
	dense_rank() over(order by score) rg_denserank,
from players;