Data model and queries for rentals and customers using SQL / PostgreSQL-CodePudding

I got similar data model as on picture below, and wanted to know following things (I am trying to learn PostgreSQL so I would appreciate statements in it):

all customers who have a rental starting in the last 30 days
all customers who have more than 5 rentals in the category ‘Production’ starting in the last 6 months
For each day of November 2021, how many rentals are active on a given day ?(a rental is active between its start and return date). Guess we can use date series.

I am not so experienced in SQL statements but wanted to get some inputs...

I did this for 1):

SELECT customer_id FROM rental
WHERE start_date > current_timestamp - interval '30 day';

and maybe solution to the 3rd:

SELECT 
   date_trunc('day', rental_date) m,
   count (customer_id)
from rental
where rental_date >= '2021-11-01'
and   return_date < '2021-12-01'
group by 
m
order by
m;

CodePudding user response：

Try these queries.

SELECT C.Name AS CusomterName,
       R.*
FROM Customers C
JOIN rentals R
  ON C.customer_id = R.customer_id 
WHERE start_date > current_timestamp - interval '30 day';


SELECT C.Name AS CusomterName,
       COUNT(*)
FROM Customers C
JOIN rentals R
  ON C.customer_id = R.customer_id 
JOIN line_items L
  ON R.id = L.rental_id
WHERE L.category = 'Production'
      AND start_date > current_timestamp - interval '6 months'
GROUP BY C.Name
HAVING COUNT(*) > 5;    


SELECT t.dd,
       COUNT(*) filter(WHERE t.dd BETWEEN R.start_date AND R.return_date) AS ActiveRentals
FROM rentals R 
CROSS JOIN (SELECT generate_series(timestamp'2021-11-01', '2021-11-30', '1 day')::DATE AS dd) t
GROUP by t.dd;