https://www.youtube.com/watch?v=NlaGsuuCitY

CREATE TABLE customer_orders (
    order_id INTEGER,
    customer_id INTEGER,
    order_date DATE,
    order_amount INTEGER
);

INSERT INTO customer_orders VALUES 
    (1, 100, '2022-01-01', 2000),
    (2, 200, '2022-01-01', 2500),
    (3, 300, '2022-01-01', 2100),
    (4, 100, '2022-01-02', 2000),
    (5, 400, '2022-01-02', 2200),
    (6, 500, '2022-01-02', 2700),
    (7, 100, '2022-01-03', 3000),
    (8, 400, '2022-01-03', 1000),
    (9, 600, '2022-01-03', 3000);

-- Include SELECT * after the INSERT statement for verification
SELECT * FROM customer_orders;

WITH
CASE WHEN
FIRST_VALUE()

with cte as (
SELECT * 
	,FIRST_VALUE(order_date) OVER(partition by customer_id order by order_date) as first_ord
FROM customer_orders
)

select order_date 
	,SUM(case when order_date = first_ord then 1 else 0 end) as new_customer
    ,SUM(case when order_date != first_ord then 1 else 0 end) as repeated
from cte
group by order_date