I have this table on SQL:

record_type | user | date
type_1      | user1| 2018-09-27T09:13:15.946545
type_2      | user1| 2018-09-27T09:13:30.052329
type_1      | user1| 2018-09-27T09:11:18.724686
type_2      | user1| 2018-09-27T08:11:18.724686
type_1      | user2| 2018-09-25T09:13:15.946545
type_2      | user2| 2018-09-25T09:13:30.052329
type_1      | user2| 2018-09-25T09:11:18.724686
type_2      | user2| 2018-09-25T08:11:18.724686

I need to count how many users for a record_type have data older than X days. Expected result for record older than a day is:

record_type | count
type_1      | 2 
type_2      | 2 

This is my query:

select record_type, count(*) 
from (select record_type, user, max(date) as MaxDate
      from table
      group by record_type, user 
      having to_timestamp(max(date), 'YYYY-MM-DD HH24:MI:SS.M') < NOW() - interval '15' day  
      order by MaxDate desc
) as test
group by record_type;

This works, but I wonder if there's a better way?

  • \$\begingroup\$ Please take a look at our SQL tag information. We require context, the schema and output of explain select. You got a good start, but it's not complete yet. Please take a good look at the link provided and improve the quality of your question. \$\endgroup\$ – Mast Oct 2 '18 at 11:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.