How would I build an SQL query to select first time deposits, second time deposits and additional deposits from a transactions table

user_5476045 0
Viewed 112

In this scenario I have two tables users and transactions. I would like to filter all the transactions for a specified time period into 3 categories, first time deposit, second time deposit and additional deposits.

To work out a first time deposit you would check if the user has no transactions before that one using the created_at field, for second time deposit they would have one other transaction before that one and for the rest they should have 2 or more before that one.

The transactions table has 2 fields we care about here:

  • user (user id)
  • created_at (time transaction was created)

Here is my attempt but I am having trouble visualising the whole query. Any ideas on how I would do this?

SELECT
  COUNT(t.id) as first_time_deposits
FROM
  transactions t
WHERE 
  status = 'approved' AND DATE(t.created_at) BETWEEN (CURDATE() - INTERVAL 0 DAY) AND CURDATE()
GROUP BY user
HAVING NOT EXISTS
  (
    SELECT
      u.id
    FROM
      transactions u
    WHERE
      u.created_at < t.created_at
  )

I use the date interval here just for filtering transactions between a day, week etc. This query doesn't work because I am trying to reference the date of outer query in the sub query. I am also missing second time deposits and additionald deposits.

Example output I am looking for:

first_time_deposits second_time_deposits additional_deposits
15 5 6

All for a selected time period.

Any help would be greatly appreciated.

mysql sql
1 Answers

This is how I'd do that. The solution works fine if, for example, "first" transactions took place at the same time. Same for others

  • "first_to_last" is a recursive query just to display numbers we need to get transactions for (1 to 3 in your case). This makes the query easy adjustable in case if you suddenly need not first 3 but first 10 transactions

  • "numbered" - ranks transactions by date

  • Main query joins first 2 CTEs and replaces numbers with words like "first", "second", and "third". I didn't find other way rather than to hardcode values.

with recursive first_to_last(step) as (
  select 1
  union all 
  select step + 1 
    from first_to_last
   where step < 3 -- how many lines to display
),
numbered as (
  select dense_rank() over(partition by user_id order by created_at) rnk, created_at, user_id
    from transactions
)

select user_id,
       concat(case when f.step = 1 then 'first_deposit: '
                   when f.step = 2 then 'second_deposit: '
                   when f.step = 3 then 'third_deposit: '
              end,
              count(rnk))
  from numbered n
  join first_to_last f
    on n.rnk = f.step
 group by user_id, f.step
 order by user_id, f.step

dbfiddle

UPD. Answer to the additional question: ". I just want the count of all first, second and any deposit that isn't first or second"

Just remove the "first_to_last" cte

with numbered as (
  select dense_rank() over(partition by user_id order by created_at) rnk, created_at, user_id
    from transactions
)

select user_id,
       concat(case when n.rnk = 1 then 'first_deposit: '
                   when n.rnk = 2 then 'second_deposit: '
                   else 'other_deposits: ' 
              end,
              count(rnk))
  from numbered n
 group by user_id, case when n.rnk = 1 then 'first_deposit: '
                   when n.rnk = 2 then 'second_deposit: '
                   else 'other_deposits: ' 
              end
 order by user_id, rnk

UPD2. output in 3 columns: first, second and others

with numbered as (
  select dense_rank() over(partition by user_id order by created_at) rnk, created_at, user_id
    from transactions
)

select 
       sum(case when n.rnk = 1 then 1 else 0 end) first_deposit,
       sum(case when n.rnk = 2 then 1 else 0 end)  second_deposit,
       sum(case when n.rnk not in (1,2) then 1 else 0 end) other_deposit
  from numbered n
user_60336010
Related