How to get users with 3 or more consecutive weeks in order?-CodePudding

I have a user table like this,

name      week_no   year_no
fb        5         2021
twitter   1         2022
twitter   2         2022
twitter   3         2022
twitter   7         2022
youtube  21         2022

I want to find the names of users who login >= 3 consecutive weeks in the same year. The week numbers will be unique for each year. For example, in the above table we can see that user twitter is logged in week_no: 1, 2, 3 in the same year 2022 thereby satisfying the condition that I am looking for.

The output I am looking for,

name        year_no
twitter     2022

You can create the sample table using,

CREATE TABLE test (
    name varchar(20),
    week_no int,
    year_no int
);
INSERT INTO test (name, week_no, year_no)
VALUES ('fb', 5, 2021), 
       ('twitter', 1, 2022),
       ('twitter', 2, 2022),
       ('twitter', 3, 2022), 
       ('twitter', 7, 2022),
       ('youtube', 21, 2022);

I am new to SQL language and I read that group by can achieve that, can someone help in what function/query we have to use to achieve the same.

select * from test group by year_no, name;

Thank you for any help in advance.

CodePudding user response：

A simple solution which will work on every MySQL version, without using windows function. Join the same table 3 times

SELECT t1.name,t1.year_no
FROM   test t1
INNER JOIN   test t2 ON t1.name=t2.name AND t1.year_no=t2.year_no
INNER JOIN   test t3 ON t1.name=t3.name AND t1.year_no=t3.year_no
WHERE  t2.week_no = t1.week_no   1 
AND    t3.week_no = t1.week_no   2

https://dbfiddle.uk/XjeXKUFE

CodePudding user response：

You may define a unique groups for consecutive weeks in the same year and aggregate them as the following:

SELECT name, year_no
FROM
(
  SELECT *, 
   week_no -
   ROW_NUMBER() OVER (PARTITION by name, year_no ORDER BY week_no) grp
 FROM test
) T
GROUP BY name, year_no, grp
HAVING COUNT(*) >= 3
ORDER BY name, year_no

See a demo.

CodePudding user response：

Window function version.
demo

WITH cte AS (
    SELECT
        name,
        week_no,
        year_no,
        lag(week_no) OVER (PARTITION BY name,
            year_no ORDER BY year_no,
            week_no) AS lag,
        lead(week_no) OVER (PARTITION BY name,
            year_no ORDER BY year_no,
            week_no) AS lead
    FROM
        testuser
)
SELECT DISTINCT
    name,
    year_no
FROM
    cte
WHERE
    lead   lag = 2 * week_no;