PostgreSQL: How to truncate and group timestamps?

Question

I am working with a database and am using the following query:

SELECT
  evt_block_time,
  COUNT(*) filter (
    WHERE
      uniswap_version = 'v1'
  ) OVER (
    ORDER BY
      evt_block_time
  ) as v1_pairs,
  COUNT(*) filter (
    WHERE
      uniswap_version = 'v2'
  ) OVER (
    ORDER BY
      evt_block_time
  ) as v2_pairs
FROM
  (
    SELECT
      'v2' as uniswap_version,
      evt_block_time
    FROM
      uniswap_v2."Factory_evt_PairCreated"
    UNION ALL
    SELECT
      'v1' as uniswap_version,
      evt_block_time
    FROM
      uniswap."Factory_evt_NewExchange"
    ORDER BY
      evt_block_time
  ) as creations

Here's a glimpse at what it returns:

I would like to do a few things. First of all, truncate the timestamps, evt_block_time, by week and then group by week.

• NOTE: I tried using date_trunc('week', evt_block_time) under each of my select statements, but it throws an error. See below:

SELECT
  date_trunc('week', evt_block_time),
  COUNT(*) filter (
    WHERE
      uniswap_version = 'v1'
  ) OVER (
    ORDER BY
      evt_block_time
  ) as v1_pairs,
  COUNT(*) filter (
    WHERE
      uniswap_version = 'v2'
  ) OVER (
    ORDER BY
      evt_block_time
  ) as v2_pairs
FROM
  (
    SELECT
      'v2' as uniswap_version,
      date_trunc('week', evt_block_time)
    FROM
      uniswap_v2."Factory_evt_PairCreated"
    UNION ALL
    SELECT
      'v1' as uniswap_version,
      date_trunc('week', evt_block_time)
    FROM
      uniswap."Factory_evt_NewExchange"
    ORDER BY
      evt_block_time
  ) as creations

which returns:

Column "evt_block_time" does not exist at line 31, position 26.

Additionally, though I guess it's not required, I would like to only query data from the last 52 weeks (1 year).

Obviously, I'm kinda new to this SQL thing but I'm trying my best. Any help whatsoever would be appreciated!

Answer 1

The problem is you're selecting evt_block_time from the subquery, but the subquery no longer contains evt_block_time, it contains date_trunc('week', evt_block_time).

To fix this, give it a name like evt_block_week and select that.

Since it's a calculated column you can't order by it, but the order by in the subquery does nothing. Remove it. If you want to apply an order, do it in surrounding query.

The orders in the count filters also do nothing, order doesn't matter for a count. Remove them.

Finally, to get the number of each version of timestamp per week, group by evt_block_week. And also order by evt_block_week.

SELECT
  evt_block_week,
  COUNT(*) filter (
    WHERE
      uniswap_version = 'v1'
  ) as v1_pairs,
  COUNT(*) filter (
    WHERE
      uniswap_version = 'v2'
  ) as v2_pairs
FROM
  (
    SELECT
      'v2' as uniswap_version,
      date_trunc('week', evt_block_time) as evt_block_week
    FROM
      uniswap_v2."Factory_evt_PairCreated"
    UNION ALL
    SELECT
      'v1' as uniswap_version,
      date_trunc('week', evt_block_time) as evt_block_week
    FROM
      uniswap."Factory_evt_NewExchange"
  ) as creations
group by evt_block_week
order by evt_block_week

If you want to only do a range of weeks, use generate_series to generate a list of weeks. If you want to see all weeks, use that as the from sub-query and left join with creations. Order and group by the generated week.

SELECT
  weeks.week,
  COUNT(*) filter (
    WHERE
      uniswap_version = 'v1'
  ) as v1_pairs,
  COUNT(*) filter (
    WHERE
      uniswap_version = 'v2'
  ) as v2_pairs
from (
  select
    generate_series(
      date_trunc('week', '2020-01-01'::date), date_trunc('week', '2020-12-31'::date), '1 week'
    ) as week
) as weeks 
left join
  (
    SELECT
      'v2' as uniswap_version,
      date_trunc('week', evt_block_time) as evt_block_week
    FROM
      uniswap_v2."Factory_evt_PairCreated"
    UNION ALL
    SELECT
      'v1' as uniswap_version,
      date_trunc('week', evt_block_time) as evt_block_week
    FROM
      uniswap."Factory_evt_NewExchange"
  ) as creations on weeks.week = evt_block_week
group by week
order by week

Demonstration.