Postgres DB query to get the count, and first and last ids by date in a single query

Question

I have the following db structure.

table
-----
 id (uuids)
date (TIMESTAMP)

I want to write a query in postgres (actually cockroachdb which uses the postgres engine, so postgres query should be fine).

The query should return a count of records between 2 dates , id of the record with latest date and id of the record with latest earliest date within that range.

So the query should return the following: count, id(of the earliest record in the range), id (of the latest record in the range)

thanks.

Answer 1

You can use row_number() twice, then conditional aggregation:

select
    no_records,
    min(id) filter(where rn_asc = 1) first_id
    max(id) filter(where rn_desc = 1) last_id
from (
    select 
        id, 
        count(*) over() no_records
        row_number() over(order by date asc)  rn_asc, 
        row_number() over(order by date desc) rn_desc
    from mytable
    where date >= ? and date < ?
) t
where 1 in (rn_asc, rn_desc)

The question marks represents the (inclusive) start and (exclusive) end of the date interval.

Of course, if ids are always increasing, simple aggregation is sufficient:

select count(*), min(id) first_id, max(id) last_id
from mytable
where date >= ? and date < ?