2

I have a SQL Server database with a transactions table with client_id, date, and is_cancelled.

I'm trying to get the client_ids that have 3 or more transactions in a row marked as is_cancelled, along with the in_a_row count. I've gotten as far as the following, which gives me a 1 for is_same when the is_cancelled supports are consecutive, and a running total of cancelled transactions (which is not quite what I need)

SELECT 
client_id,
date,
is_same,
SUM(is_same) OVER (PARTITION BY client_id ORDER BY date) AS sum_same,
transCancelled
FROM
(
    SELECT
    client_id,
    LAG(is_cancelled) OVER (PARTITION BY client_id ORDER BY date) AS previous_cancelled,
    CASE 
        WHEN is_cancelled = LAG(is_cancelled) OVER (PARTITION BY client_id ORDER BY date)
        THEN 1
        ELSE 0
    END as is_same,
    date,
    is_cancelled
    FROM transactions
    WHERE deleted_at IS NULL -- Ignore soft-deleted rows
) AS t_01
WHERE previous_cancelled = 1
ORDER BY date

Fiddle with sample data: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=a0c9b12203ab2d0c83f73604ccc9d0a0

Expected data (client_id, count) 1, 3 3, 6

3
  • Sample data (as CREATE TABLE INSERT statements) and expected results would help Feb 14 at 1:03
  • Looks like you just need to group by sum_same and filter HAVING COUNT(*) > 3, you will need to subquery again for this Feb 14 at 1:06
  • Added a DB Fiddle Feb 14 at 2:11

1 Answer 1

4

This is a type of gaps-and-islands problem.

The key to most solutions of this type, is to count the changes, so you want an is_different column, not is_same. Then you conditionally count that column (easier if you use NULL instead of 0) to create an ID for each group of rows.

It's unclear exactly what final results you want, but by grouping up that result, you can get the maximum and minimum number of consecutive rows, as well as the count of actual row-groups, per client_id:

WITH PrevValues AS (
    SELECT
      client_id,
      LAG(is_cancelled) OVER (PARTITION BY client_id ORDER BY date) AS previous_cancelled,
      CASE WHEN is_cancelled = LAG(is_cancelled) OVER (PARTITION BY client_id ORDER BY date)
        THEN NULL ELSE 1 END
        as is_different,
      date,
      is_cancelled
    FROM transactions
),
Grouped AS (
    SELECT 
      client_id,
      date,
      is_different,
      COUNT(is_different) OVER (PARTITION BY client_id ORDER BY date ROWS UNBOUNDED PRECEDING) AS group_id
    FROM PrevValues
),
ByGroups AS (
    SELECT
      client_id,
      COUNT(*) as in_a_row
    FROM Grouped
    GROUP BY
      client_id,
      group_id
    HAVING COUNT(*) >= 3
)
SELECT
  client_id,
  MAX(in_a_row) as max_in_a_row,
  MIN(in_a_row) as min_in_a_row,
  COUNT(*) as num_groups
FROM ByGroups
GROUP BY
  client_id;

db<>fiddle

Note that your sample data has rows with identical dates, and is one reason you should always use ROWS UNBOUNDED PRECEDING (the default for ordered window functions is RANGE UNBOUNDED PRECEDING which is subtly different). In any event, you should always try to have deterministic ordering.

3
  • Thanks for the response. It's a bit late so I'll try it in the morning, but row-groups (of 3 or more consecutive) sounds like exactly what I'm after. I have duplicate dates because they can have multiple transactions in a day - there's also a start_time that can be used for ordering but figured it would just complicate things. Feb 14 at 12:02
  • Alrighty with this data set dbfiddle.uk/… client_id gets 4 in a row when it should be 0 since there's never a group of 3 cancelled transactions in a row. There are 4 NON cancelled in a row Feb 15 at 1:36
  • Just added a WHERE is_cancelled = 1 to the Grouped query, thanks! Feb 15 at 2:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.