Выбрать минимальную дату, плюс N дней?

Question

[XenK]

Есть такая таблица, с бонусами у пользователя:

╔═════════╦════════╦════════════════════════╗
║ user_id ║ amount ║ created_at             ║
╠═════════╬════════╬════════════════════════╣
║ 1       ║ 50     ║ 2018-09-02 16:59:58.10 ║
╠═════════╬════════╬════════════════════════╣
║ 1       ║ 120    ║ 2018-09-09 12:59:58.10 ║
╠═════════╬════════╬════════════════════════╣
║ 3       ║ 25     ║ 2018-07-05 16:59:58.10 ║
╠═════════╬════════╬════════════════════════╣
║ 3       ║ 90     ║ 2018-07-07 16:59:58.10 ║
╚═════════╩════════╩════════════════════════╝

Нужно получить сумму amount для каждого пользователя, с момента первого получения бонуса + 5 дней. Как правильно это сделать?

Comments

[sim3x]

Какая субд?

[XenK]

sim3x, postgres

Answer 1

[nozzy]

select
t1.user_id,
sum(t1.amount),
count(*) as count
from your_table t1
inner join (
    select
    t2.user_id,
    t2.created_at
    from your_table t2
    where not exists (
        select 1 
        from your_table
        where user_id = t2.user_id
        and created_at < t2.created_at
    )
)   t3 on t3.user_id = t1.user_id 
    and t1.created_at between t3.created_at and t3.created_at + interval 5 day
group by t1.user_id
having(count <= 5)

Answer 2

[Дмитрий Телепнев]

select
    table1.user_id, count(table1.amount)
from table1
join (
    select
        user_id, min(created_at) minimal_date
    from table1
    group by user_id
)dates on dates.user_id = table1.user_id
    and table1.created_at between dates.minimal_date and (dates.minimal_date + interval '5d')
group by table1.user_id

Answer 3

[kylemaclohlan]

WITH table1 ( user_id, amount, created_at)
AS (VALUES
      (1, 50,  '2018-09-02 16:59:58.10' :: TIMESTAMP)
    , (1, 120, '2018-09-09 12:59:58.10' :: TIMESTAMP)
    , (3, 25,  '2018-07-05 16:59:58.10' :: TIMESTAMP)
    , (3, 90,  '2018-07-07 16:59:58.10' :: TIMESTAMP)
)
SELECT t1.user_id,sum(t1.amount)
FROM table1 t1
JOIN LATERAL (SELECT min(t2.created_at) AS mi
              FROM table1 t2
              WHERE t2.user_id = t1.user_id
     ) t3 ON TRUE
WHERE t1.created_at - t3.mi <= '5 day'
GROUP BY t1.user_id
;