CODEWITHSUNDEEP
sqlpostgresql
Arya
Arya

Reputation: 8985

getting average of inserts per day

I have the following table

CREATE TABLE public.listing
(
  id bigint NOT NULL DEFAULT nextval('listing_id_seq'::regclass),
  listing_url text NOT NULL,
  insert_time timestamp with time zone DEFAULT now(),
  CONSTRAINT listing_pkey PRIMARY KEY (id),
  CONSTRAINT listing_listing_url UNIQUE (listing_url)
)

How can I get the average number of rows that were inserted per day for the past week?

Upvotes: 0

Views: 100

Answers (2)

Adarsh Ravi
Adarsh Ravi

Reputation: 953

select COUNT(*)/7 from Listing where insert_time between '2016-09-01' and '2016-09-07' group by insert_time

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

I think you can just take the number of records appearing in the past week, and divide by 7 for the number of days in a week:

select count(*) / 7
from public.listing
where insert_time > current_date - interval '7 days'   -- inserts happening in past week

Upvotes: 1

Related Questions