I just started learning Postgres, and I'm trying to make an aggregation table that has the columns:

• user_id
• booking_sequence
• booking_created_time
• booking_paid_time
• booking_price_amount
• total_spent

All columns are provided, except for the booking_sequence column. I need to make a query that shows the first five flights of each user that has at least x purchases and has spent more than a certain amount of money, then sort it by the amount of money spent by the user, and then sort it by the booking sequence column.

I've tried :

select user_id,
       row_number() over(partition by user_id order by user_id) as booking_sequence,
       booking_created_time as booking_created_date,
       booking_price_amount,
       sum(booking_price_amount) as total_booking_price_amount
from fact_flight_sales 
group by user_id, booking_created_time, booking_price_amount 
having count(user_id) > 5
   and total_booking_price_amount > 1000
order by total_booking_price_amount;

I got 0 when I added count(user_id) > 5, and total_booking_price_amount is not found when I add the second condition in the HAVING clause.

Edit:
I managed to make the code function correctly, for those who are curious:

select x.user_id, row_number() over(partition by x.user_id) 
as booking_sequence, x.booking_created_time::date as booking_created_date, x.booking_price_amount, 
sum(y.booking_price_amount) as total_booking_price_amount from 
(
    select user_id, booking_created_time, booking_price_amount from fact_flight_sales 
    group by user_id, booking_created_time, booking_price_amount
) as x
join 
(
    select user_id, booking_price_amount 
    from fact_flight_sales group by user_id, booking_price_amount
) as y
on x.user_id = y.user_id
group by x.user_id, x.booking_created_time, x.booking_price_amount 
having count(x.user_id) >= 1 and sum(y.booking_price_amount) >250000 
order by total_booking_price_amount desc, booking_sequence asc;

Big thanks to Laurenz for the help!

CodePudding user response：

About count(user_id) > 5:

HAVING is calculated before window functions are evaluated, So result rows excluded by the HAVING clause will not be used to calculate the window function.

About total_booking_price_amount in HAVING:

You cannot use aliases from the SELECT list in the HAVING clause. You will have to repeat the expression (or use a subquery).