PostgreSQL showing different time periods in a single query

user_9095681 0
Viewed 66

I have a query that will return the ratio of issuances from (issuances from specific network with specific time period / total issuances). so the issuances from specific network with a specific time period divided to total issuances from all networks. Right now it returns the ratios of issuances only from last year (year-to-date I mean), I want to include several time periods in it such as one month ago, 2 month ago etc. LEFT JOIN usually works but I couldn't figure it out for this one. How do I do it?

Here is the query:

SELECT IR1.network,
count(*) / ((select count(*) FROM issuances_extended  
where status = 'completed' and 
issued_at >= date_trunc('year',current_date)) * 1.) as issuance_ratio_ytd

FROM issuances_extended as IR1 WHERE status = 'completed' and
(issued_at >= date_trunc('year',current_date))

GROUP BY
IR1.network

order by IR1.network
postgresql sql
3 Answers

I would break your query into CTEs something like this:

with periods (period_name, period_range) as (
  values 
    ('YTD',   daterange(date_trunc('year', current_date), null)),
    ('LY',    daterange(date_trunc('year', current_date - 'interval 1 year'),
                        date_trunc('year', current_date))),
    ('MTD',   daterange(date_trunc('month', current_date - 'interval 1 month'),
                        date_trunc('month', current_date));
  -- Add whatever other intervals you want to see
), period_totals as ( -- Get period totals
  select p.period_name, p.period_range, count(*) as total_issuances
    from periods p
         join issuances_extended i
           on i.status = 'completed' 
          and i.issued_at <@ p.period_range
)
select p.period_name, p.period_range, 
       i.network, count(*) as network_issuances,
       1.0 * count(*) / p.total_issuances as issuance_ratio
  from period_totals p
       join issuances_extended i
         on i.status = 'completed'
        and i.issued_at <@ p.period_range
 group by p.period_name, p.period_range, i.network, p.total_issuances;

The problem with this is that you get rows instead of columns, but you can use a spreadsheet program or reporting tool to pivot if you need to. This method simplifies the calculations and lets you add whatever period ranges you want by adding more values to the periods CTE.

user_138083190

Something like this? Obviously not tested

 SELECT
    IR1.network,
    count(*)/((select count(*) FROM issuances_extended  
      where status = 'completed' and 
      issued_at between mon.t and current_date ) * 1.) as issuance_ratio_ytd
FROM 
    issuances_extended as IR1 ,
    (
        SELECT
            generate_series('2022-01-01'::date, 
                            '2022-07-01'::date, '1 month') AS t) 
    AS mon
WHERE 
    status = 'completed' and
    (issued_at between mon.t and current_date)
GROUP BY
    IR1.network
ORDER BY
    IR1.network
user_70706130

I've managed to join these tables, so I am answering my question for those who would need some help. To add more tables all you have to do is put new queries in LEFT JOIN and acknowledge them in the base query (IR3, IR4, blabla etc.)

SELECT
IR1.network,
count(*) / (
(
select
count(*)
FROM
issuances_extended
where
status = 'completed'
and issued_at >= date_trunc('year', current_date)
) * 1./ 100
) as issuances_ratio_ytd,
max(coalesce(IR2.issuances_ratio_m0, 0)) as issuances_ratio_m0

FROM
issuances_extended as IR1

LEFT JOIN (
SELECT
network,
count(*) / (
(
select
count(*)
FROM
issuances_extended
where
status = 'completed'
and issued_at >= date_trunc('month', current_date)
) * 1./ 100
) as issuances_ratio_m0
FROM
issuances_extended
WHERE
status = 'completed'
and (issued_at >= date_trunc('month', current_date))
GROUP BY
network
) AS IR2 ON IR1.network = IR2.network

WHERE
status = 'completed'
and (issued_at >= date_trunc('year', current_date))

GROUP BY
IR1.network,
IR2.issuances_ratio_m0
order by
IR1.network
user_90956810
Related