Intermediate sql ad hoc conditional aggregation query not working

I am following the above mission : intermediate sql

and tried out this ad-hoc query to find repeat buyers or first time, the inner query works , but outer does not work please advise?

I found the query here

SELECT 
    invoice_date,
    SUM(CASE WHEN t.row_number = 1 THEN t.amount else 0 END) as NEW_buyer
    SUM(CASE WHEN t.row_number <> 1 THEN t.amount else 0 END) as REPEAT_buyer
    
    FROM
    (
    SELECT
            i.customer_id,
            i.invoice_date,
            ROW_NUMBER() OVER (PARTITION BY i.customer_id),
            total
        FROM invoice i
    
    )t
    
    GROUP BY 1
    ORDER BY 1
1 Like
  • syntax error after NEW_buyer in case
  • it will prompt for t.amount and t.row_number, columns will have to be renamed in inner query.
2 Likes

thanks, i got it now.