CODEWITHSUNDEEP
sqlpostgresqlpercentage
Sergii Bishyr
Sergii Bishyr

Reputation: 8641

Postgresql percentage of records created in the specific time interval

I have a table that contains the field created_at. I want to calculate the percentage of records from the total number that was created in the specified time interval. Let's say that I have the following structure:

| name   | created_at                   |
----------------------------------------
| first  | "2019-04-29 09:30:07.441717" |
| second | "2019-04-30 09:30:07.441717" |
| third  | "2019-04-28 09:30:07.441717" |
| fourth | "2019-04-27 09:30:07.441717" |

So I want to calculate what is the percentage of records created in the time interval between 2019-04-28 00:00:00 and 2019-04-30 00:00:00. In this time interval, I have two records first and third, so the result should be 50%. I came across the OVER() clause, but either I don't get how to use it, or it's not what I need.

Upvotes: 0

Views: 293

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I would just use avg():

select avg( (created_at between '2019-04-28' and '2019-04-30')::int )
 from your_table

You can multiply by 100, if you want a value between 0 and 1.

I strongly discourage you from using between with date/time values. The time components may not behave the way you want. You used "between" in your question, but I left it in. However, I would suggest:

select avg( (created_at >= '2019-04-28' and 
             created_at < '2019-04-30'
            )::int
          )
 from your_table;

It is not clear if you want < '2019-04-30', <= '2019-04-30' or '2019-05-01'.

Upvotes: 1

Radim Bača
Radim Bača

Reputation: 10701

You can use CASE

 select 100 * count(case 
      when created_at between '2019-04-28 00:00:00' and '2019-04-30 00:00:00' 
      then 1 
      end) / count(*)
 from your_table

Upvotes: 2

Related Questions