Как посчитать уникальные заказы за период?

Question

[WHATsMyName]

Задание: Для каждой даты в таблице user_actions посчитайте количество первых заказов, совершённых пользователями.
Первыми заказами будем считать заказы, которые пользователи сделали в нашем сервисе впервые. В расчётах учитывайте только неотменённые заказы.
В результат включите две колонки: дату и количество первых заказов в эту дату. Колонку с датами назовите date, а колонку с первыми заказами — first_orders.
Результат отсортируйте по возрастанию даты.
Поля в результирующей таблице: date, first_orders
Пояснение:
Учитывайте, что у каждого пользователя может быть всего один первый заказ (что вполне логично).

WITH
subquery_1 AS (
    SELECT 
        MAX(time)::DATE as date,
        order_id
    FROM   
        user_actions
    WHERE
        order_id NOT IN (
            SELECT 
                order_id
            FROM   
                user_actions
            WHERE  
                action = 'cancel_order')
    GROUP BY 
        order_id
    )
                    
SELECT 
    date,
    COUNT(DISTINCT order_id) AS first_orders
FROM (   
    SELECT
        *
    FROM
        subquery_1) AS t 
GROUP BY 
    date
ORDER BY 
    date

Выдает больше записей, не только один заказ.
Мой вывод:

date | first_orders
24/08/22	| 138	
25/08/22	| 1,059	
26/08/22	| 1,447

Ожидаемый:

date | first_orders
24/08/22	| 127
25/08/22	| 802
26/08/22	| 984

Comments

[Akina]

Вы точно уверены, что MAX(time) вернёт дату самого первого заказа?

[WHATsMyName]

Akina, спасибо за обратную связь. Да, я уверен. На мой взгляд ошибка в том, что не у каждого пользователя указывается один заказ, некоторые юзеры дублируются. Как исключить повторения подсчета order_id для каждого user_id?

[Дмитрий]

WHATsMyName, а мне тоже кажется что вы зря уверены

[WHATsMyName]

Дмитрий, возможно ошибаюсь. Попытался сделать проверку, не получилось. Какие ваши мысли по поводу решения задачи?

[Дмитрий]

select t.date, count(*) from (SELECT 
        min(time)::DATE as date,
        user_id
    FROM   
        user_actions
    WHERE
                action != 'cancel_order'
    GROUP BY 
        user_id
    ) as t
group by date;

[WHATsMyName]

Дмитрий, мое почтение! Премного благодарен.

[Дмитрий]

WHATsMyName, да не за что. но не могу не отметить что согласно правилам ресурса не стоит использовать этот ресурс для решения задач.

[WHATsMyName]

Дмитрий, буду знать, спасибо