CODEWITHSUNDEEP
sqlpostgresql
Hyder
Hyder

Reputation: 31

Count records for a user for a given timeframe following a date

So I have the following table:

User Date
A 2020-01-05
A 2020-07-01
A 2020-09-01
A 2020-12-31
B 2020-03-01
B 2020-11-05

But what I want to do is:

  1. Take the minimum (first) date for each user, and then
  2. Count the number of records for that user after that minimum (first) date

Below is an example of what I want to achieve using PostgreSQL:

User min_date count_of_records_in_6_months
A 2020-01-05 2
B 2020-03-01 0

What I've tried thus far is below (obviously this is just giving me a total count of each user).

SELECT 
user
,MIN(date) as min_date
,count(*) as count_of_records
FROM myTable
GROUP BY user

Upvotes: 0

Views: 205

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270623

You can use window functions:

SELECT user,
       COUNT(*) FILTER (WHERE date <= min_date + interval '6 month')
FROM (SELECT t.*, MIN(date) OVER (PARTITION BY user) as min_date
      FROM myTable t
     ) t
GROUP BY user;

Here is a db<>fiddle.

Upvotes: 1

Related Questions