Which SQL Do You Prefer?

cte_or_subquery.sql

/* 
Suppose tb1 contains 31 rows:
  day (Jan 1 to Jan 31) 
  mins (mins generated that day)

And I want to know the percent distribution of mins for each day in January (i.e., (mins / total_Jan_mins) ). 
*/

-- Query 1 (CTE)

with daily_mins as (
  select
    day,
    mins,
    month(day) as month
  from {{ ref('tb1') }}
),

sums as (
  select
    month,
    sum(mins) as total_mins
  from daily_mins
  group by month
)

select
  daily_mins.*,
  total_mins,
  mins/total_mins as pct
from daily_mins
inner join sums on sums.month = daily_mins.month


-- Query 2 (Subquery)

with mins as (
  select
    *,
    (select (select sum(mins) from {{ ref('tb1') }}) as total_mins,
    mins/(select sum(mins) from {{ ref('tb1') }}) as pct
  from {{ ref('tb1') }}
)

select *
from mins