SQLite Group by last seven days (not weekly!)-CodePudding

I was looking some GROUP BY questions here but no one is like mine.

I have a current table like this:

|day   |  client|
----------------
|2020-01-07|id11|
|2020-01-07|id10|
|2020-01-06|id09|
|2020-01-06|id08|
|2020-01-05|id07|
|2020-01-04|id06|
|2020-01-03|id05|
|2020-01-03|id04|
|2020-01-02|id03|
|2020-01-01|id02|
|2020-01-01|id01|

And I want to create a new column with the ocurrences of unique clients for the last seven days (day - 6) (not weekly!) and show it by day:

|day   |last 7 day clients|
----------------
|2020-01-07|11|
|2020-01-06| 9|
|2020-01-05| 7|
|2020-01-04| 6|
|2020-01-03| 5|
|2020-01-02| 3|
|2020-01-01| 2|

All the aswers here are grouping weekly!

What I tryed:

pd.read_sql_query("""SELECT DATE(day) dateColumn,  
                            COUNT(DISTINCT client) AS seven_day_users
                    FROM table
                    GROUP BY date(dateColumn, '-6 days')
                    ORDER BY dateColumn DESC;
                        """, conn)

But the result is grouping by day, not into the interval.

CodePudding user response：

Use a self join of the table and aggregation:

SELECT t1.day,
       COUNT(DISTINCT t2.client) `last 7 day clients`
FROM tablename t1 INNER JOIN tablename t2
ON t2.day BETWEEN date(t1.day, '-6 day') AND t1.day
GROUP BY t1.day
ORDER BY t1.day DESC;

See the demo.