dean2020 dean2020 - 23 days ago 12
MySQL Question

MySQL count / track streaks or consecutive dates

+-----+------------+------------+---------------------+
| id | seller_id | prod_id | date |
+-----+------------+----------------------------------+
| 1 | 283 | 4243 | 2016-10-10 23:55:01 |
| 2 | 287 | 4243 | 2016-10-10 02:01:06 |
| 3 | 283 | 4243 | 2016-10-11 23:55:06 |
| 4 | 311 | 4243 | 2016-10-11 23:55:07 |
| 5 | 283 | 4243 | 2016-10-12 23:55:07 |
| 6 | 283 | 4243 | 2016-10-13 23:55:07 |
| 7 | 311 | 4243 | 2016-10-13 23:55:07 |
| 8 | 287 | 4243 | 2016-10-14 23:57:06 |
| 9 | 311 | 4243 | 2016-10-14 23:57:06 |
| 10 | 311 | 4243 | 2016-10-15 23:57:06 |
+-----+------------+------------+---------------------+


From the table above how would I extract the following information using an MySQL query?

+------------+---------+----------------+---------------+
| seller_id | prod_id | streak in days | begin streak |
+-----+------------+--------------------+---------------+
| 283 | 4243 | 4 | 2016-10-10 |
| 287 | 4243 | 1 | 2016-10-10 |
| 311 | 4243 | 1 | 2016-10-11 |
| 311 | 4243 | 3 | 2016-10-13 |
| 287 | 4243 | 1 | 2016-10-14 |
+------------+---------+----------------+---------------|


So basically I need to identify each block of consecutive dates for each seller (seller_id) selling products (prod_id).

I limited this example to 1 prod_id and only a range of a few days, but sellers do sell more than 1 product (prod_id)

Answer
SELECT
  seller_id
  ,prod_id
  ,COUNT(*) as StreakInDays
  ,MIN(DateCol) as BeginStreak
FROM
  (
    SELECT
      seller_id
      ,prod_id
      ,DATE(DateCol) as DateCol
      ,(@rn:= if((@seller = seller_id) AND (@prod = prod_id), @rn + 1,
                 if((@seller:= seller_id) AND (@prod:= prod_id), 1, 1)
                  )
        ) as RowNumber
    FROM
      Transact t
      CROSS JOIN (SELECT @seller:=0, @prod:=0, @rn:=0) var
    ORDER BY
      seller_id
      ,prod_id
      ,DATE(DateCol)
    ) t    
GROUP BY
  seller_id
  ,prod_id
  ,DATE_SUB(DateCol, INTERVAL RowNumber Day)
ORDER BY
  prod_id
  ,DATE_SUB(DateCol, INTERVAL RowNumber Day)
  ,seller_id

Generate a partitioned row number partitioned by seller_id and prod_id. Then use the Date - RownNumber as a grouping and you can get to your answer by simple aggregation.

SQL Fiddle to show you it works for multiple products, sellers etc. http://sqlfiddle.com/#!9/0a0c44/8/0

Note if it is possible that the same seller can have more than 1 transaction for a product on the same day then you will need to replace the Transact with a derived table of DISTINCT seller_id, prod_id, DATE(date) before generating the row number like this:

SELECT
  seller_id
  ,prod_id
  ,COUNT(*) as StreakInDays
  ,MIN(DateCol) as BeginStreak
FROM
  (
    SELECT
      seller_id
      ,prod_id
      ,DateCol
      ,(@rn:= if((@seller = seller_id) AND (@prod = prod_id), @rn + 1,
                 if((@seller:= seller_id) AND (@prod:= prod_id), 1, 1)
                  )
        ) as RowNumber
    FROM
     (SELECT DISTINCT seller_id, prod_id, DATE(DateCol) as DateCol
       FROM
         Transact
      )t
      CROSS JOIN (SELECT @seller:=0, @prod:=0, @rn:=0) var
    ORDER BY
      seller_id
      ,prod_id
      ,DateCol
    ) t    
GROUP BY
  seller_id
  ,prod_id
  ,DATE_SUB(DateCol, INTERVAL RowNumber Day)
ORDER BY
  prod_id
  ,DATE_SUB(DateCol, INTERVAL RowNumber Day)
  ,seller_id

http://sqlfiddle.com/#!9/0a0c44/11

Comments