包丁一本さらしに巻いて

with nums as (
  select generate_series(1, 30) n
), registration as (
  select
    to_char(u.registered_at at time zone 'JST', 'YYYY/MM') mt
    , to_char(u.registered_at at time zone 'JST', 'DD') dt
    , count(*) cnt
  from your_service_user u
  where u.registered_at >= '2019-09-01 00:00:00' at time zone 'JST'
  and u.registered_at < '2019-12-01 00:00:00' at time zone 'JST'
  group by mt, dt
)

select
  r.mt mt
  , r.dt dt
  , sum(r.cnt) over (partition by r.mt order by r.dt) cumsum
from registration r
union
select
  'target' as mt
  , lpad(n::text, 2, '0') dt
  , 100000 / 30 * n as cumsum
from nums

with recursive generate_series(x) as (
 select 1
 union all
 select x+1 from generate_series limit 30
), t as (
  select
    date
    , sum(cost) cost
  from query_xxx
  group by date
)
select
  '2019/11' as mt
  , strftime('%d', a.date) date
  , sum(b.cost) total_cost
from t as a
cross join t as b 
where b.date <= a.date
group by a.date
union
select
  'target' as mt
  , substr('00' || x, -2, 2) date
  , (20000000 / 30) * x total_cost
from generate_series
order by date

with t as (
  select 
    date_trunc('day', are.registered_at at time zone 'JST') dt
    , case
         when are.network_name in ('Organic', 'other-organic-network') then 'organic'
         else 'paid'
       end as category
    , count(*) cnt
    , sum(count(*)) over (partition by date_trunc('day', are.registered_at at time zone 'JST')) total
  from adjust_registration_event are
  where are.registered_at >= date_trunc('day', current_timestamp + '-3 months')
  group by dt, category
)
select
  paid.dt
  , coalesce(paid.cnt, 0) paid_cnt
  , coalesce(organic.cnt, 0) organic_cnt
  , coalesce(organic.cnt, 0) + coalesce(paid.cnt, 0) total_cnt
  , organic.organic_rate
from (
  select
    t.dt
    , t.cnt cnt
  from t
  where t.category = 'paid'
) paid
left join (
  select
    t.dt
    , t.cnt cnt
    , cast(t.cnt as float) / cast(t.total as float) organic_rate
  from t
  where t.category = 'organic'
) organic
on organic.dt = paid.dt
order by paid.dt
;

select
  a.dt
  , a.hr
  , sum(a.cnt) over (partition by a.dt order by a.hr)
from (
  select
    to_char(date_trunc('day', i.registered_at) at time zone 'JST', 'YYYY/MM/DD') dt
    , to_char(date_trunc('hour', i.registered_at), 'HH24') hr
    , count(*) cnt
  from your_service_user i
  where i.registered_at >= date_trunc('day', current_timestamp at time zone 'JST' + '-3days')
  group by dt, hr
) a

select
  a.dt
  , a.hr
  , a.w
  , sum(a.cnt) over (partition by a.dt order by a.hr)
from (
  select
    to_char(date_trunc('day', e.registered_at at time zone 'JST'), 'YYYY/MM/DD') dt
    , to_char(e.registered_at at time zone 'JST', 'HH24') hr
    , extract(dow from e.registered_at at time zone 'JST') w
    , count(*) cnt
  from your_service_user e
  where e.registered_at >= date_trunc('day', current_timestamp at time zone 'JST' + '-3weeks')
  and extract(dow from e.registered_at at time zone 'JST') = extract(dow from current_timestamp at time zone 'JST')
  group by dt, hr, w
) a

select
  to_char(date_trunc('day', i.registered_at at time zone 'jst'), 'YYYY/MM/DD') dt
  , to_char(date_trunc('hour', i.registered_at at time zone 'jst'), 'HH24') hr
  , count(*) cnt
from coolec_user i
where i.registered_at >= date_trunc('day', current_timestamp at time zone 'jst' + '-3days')
group by dt, hr
order by dt, hr

select
  inst.dt
  , inst.hr
  , cast(reg.cnt as float) / cast(inst.cnt as float) registration_rate
from (
  select
    to_char(date_trunc('day', i.installed_at) at time zone 'JST', 'YYYY/MM/DD') dt
    , to_char(date_trunc('hour', i.installed_at), 'HH24') hr
    , count(*) cnt
  from adjust_install_event i
  where i.installed_at >= date_trunc('day', current_timestamp at time zone 'JST' + '-3days')
  group by dt, hr
) inst
join (
  select
    to_char(date_trunc('day', i.registered_at) at time zone 'JST', 'YYYY/MM/DD') dt
    , to_char(date_trunc('hour', i.registered_at), 'HH24') hr
    , count(*) cnt
  from adjust_registration_event i
  where i.registered_at >= date_trunc('day', current_timestamp at time zone 'JST' + '-3days')
  group by dt, hr
) reg
on (reg.dt = inst.dt and reg.hr = inst.hr)
order by inst.dt, inst.hr

select
  td.network_name
  , td.cnt tdy
  , coalesce(ye.cnt, 0) yst
  , td.cnt - coalesce(ye.cnt, 0) diff
  , case
      when ye.cnt = 0 then 0
      else abs((td.cnt - coalesce(ye.cnt, 0))) / cast(ye.cnt as float)
    end rate
  , case
      when sign(td.cnt - coalesce(ye.cnt, 0)) = 1 then '<div class="bg-success text-center">↑</div>'
      when sign(td.cnt - coalesce(ye.cnt, 0)) = 0 then '<div class="bg-info text-center">→</div>'
      when sign(td.cnt - coalesce(ye.cnt, 0)) = -1 then '<div class="bg-danger text-center">↓</div>'
    end status
from (
  select
    re.network_name
    , count(*) cnt
  from adjust_registration_event re
  where re.registered_at >= date_trunc('day', current_timestamp at time zone 'jst')
  and re.registered_at <= date_trunc('minutes', current_timestamp at time zone 'jst')
  group by re.network_name
) td
left join (
  select
    re.network_name
    , count(*) cnt
  from adjust_registration_event re
  where re.registered_at >= date_trunc('day', current_timestamp at time zone 'jst' + '-1day')
  and re.registered_at <= date_trunc('minutes', current_timestamp at time zone 'jst' + '-1day')
  group by re.network_name
) ye
on td.network_name = ye.network_name
order by abs(td.cnt - coalesce(ye.cnt, 0)) desc