+------------------+------+
| Column Name | Type |
+------------------+------+
| transaction_id | int |
| customer_id | int |
| transaction_date | date |
| amount | int |
+------------------+------+
transaction_id is the column with unique values of this table.
Each row contains information about transactions that includes unique (customer_id, transaction_date) along with the corresponding customer_id and amount.
Write a solution to find all customer_id who made the maximum number of transactions on consecutive days.
Return all customer_id with the maximum number of consecutive transactions.
Order the result table by customer_id in ascending order.
Input:
Transactions table:+----------------+-------------+------------------+--------+| transaction_id | customer_id | transaction_date | amount |+----------------+-------------+------------------+--------+|1|101|2023-05-01|100||2|101|2023-05-02|150||3|101|2023-05-03|200||4|102|2023-05-01|50||5|102|2023-05-03|100||6|102|2023-05-04|200||7|105|2023-05-01|100||8|105|2023-05-02|150||9|105|2023-05-03|200|+----------------+-------------+------------------+--------+Output:
+-------------+| customer_id |+-------------+|101||105|+-------------+Explanation:
- customer_id 101 has a total of 3 transactions, and all of them are consecutive.- customer_id 102 has a total of 3 transactions, but only 2 of them are consecutive.- customer_id 105 has a total of 3 transactions, and all of them are consecutive.In total, the highest number of consecutive transactions is3, achieved by customer_id 101 and 105. The customer_id are sorted in ascending order.
The key idea is to use window functions to identify consecutive days for each customer. By assigning a row number to each transaction per customer and comparing it to the transaction date, we can group consecutive days together. The difference between the row number and the date (as an integer) will be constant for consecutive days, allowing us to group and count them.
WITH ranked AS (
SELECT customer_id,
transaction_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDERBY transaction_date) AS rn
FROM Transactions
),
consec AS (
SELECT customer_id,
DATE_SUB(transaction_date, INTERVAL rn DAY) AS grp,
COUNT(*) AS len
FROM ranked
GROUPBY customer_id, grp
),
maxlen AS (
SELECT customer_id, MAX(len) AS max_consec FROM consec GROUPBY customer_id
),
global_max AS (
SELECTMAX(max_consec) AS gmax FROM maxlen
)
SELECT m.customer_id
FROM maxlen m, global_max gWHERE m.max_consec =g.gmax
ORDERBY m.customer_id;
WITH ranked AS (
SELECT customer_id,
transaction_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDERBY transaction_date) AS rn
FROM Transactions
),
consec AS (
SELECT customer_id,
transaction_date::date - rn * INTERVAL '1 day'AS grp,
COUNT(*) AS len
FROM ranked
GROUPBY customer_id, grp
),
maxlen AS (
SELECT customer_id, MAX(len) AS max_consec FROM consec GROUPBY customer_id
),
global_max AS (
SELECTMAX(max_consec) AS gmax FROM maxlen
)
SELECT m.customer_id
FROM maxlen m, global_max gWHERE m.max_consec =g.gmax
ORDERBY m.customer_id;