10. Min and max spend

Hi Sai,

The problem is that HAVING works with only aggregation functions, and extract is not an aggregation function, you need to replace the HAVING to WHERE and put it right after FROM statement.

Hope it helps,

Leon

with cte as (
  select *, 
  sum(amount) over(partition by customer_id) as amt
  FROM payment
  where extract(month from payment_ts) = 6 and 
  extract(year from payment_ts) = 2020
)

SELECT min(amt) as min_spend, max(amt) as max_spend
FROM cte
where extract(month from payment_ts) = 6 and 
extract(year from payment_ts) = 2020
LIMIT 5;

- using temp table where we take the data of total spend of june by a customer in 2020 and then using that temp data to calculate max and min spend in the month of june and year 2020.

select min(total_spend) as min_spend,max(total_spend) as max_spend
from(
select customer_id,sum(amount) as total_spend from payment
where extract(month from payment_ts)=6 AND extract(year from payment_ts)=2020
group by customer_id) subquery;

%MCEPASTEBIN%

SELECT
    MIN(total_spend) AS min_spend,
    MAX(total_spend) AS max_spend

FROM (
    SELECT 
          customer_id,
          SUM(amount) as total_spend
      FROM payment 
      WHERE EXTRACT (MONTH FROM payment_ts) = 6 AND
    EXTRACT (YEAR FROM payment_ts) = 2020
    GROUP BY customer_id
) as skibidi