Sqlite3 window frame partition by datetime not working-CodePudding

I'm struggling with a window frame coding with Python's sqlite3. Basic I have this dataframe:

And I want to group all the clients (buy and sell) made in the same day and week. I'm working with the follow sqlite3 code:

pd.read_sql_query("""SELECT strftime('%Y-%m-%d', execution_time) AS dia,
                            COUNT(customer_id) AS cons_dia,
                            SUM(cons_dia) OVER
                                PARTITION BY dia
                                ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS seven_day
                        FROM trades
                        ORDER BY dia DESC;""", conn)

I'm not sure what am I doing wrong, it says there is a problem around "dia" (I think it means the line PARTITION BY dia. Sqlite3 does not recognize daytime, that's why I need to to the strftime('%Y-%m-%d', execution_time) in the first row.

Thank you for the help!

CodePudding user response：

The derived columns cons_dia and dia can't be used in the same SELECT statement.
Instead you should use the expressions from which they are derived:

SELECT date(execution_time) AS dia,
       COUNT(customer_id) AS cons_dia,
       SUM(COUNT(customer_id)) OVER (
         ORDER BY date(execution_time)
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS seven_day
FROM trades
GROUP BY dia
ORDER BY dia DESC;

Note that you need an ORDER BY clause inside the OVER() clause of the SUM() window function and not a PARTITION BY clause, which should be extended to 6 rows before the current row if you want a 7 day window.
Also, this will work only if there are no gaps between the dates in the table.