Ufdjrw

So, there is an account number and we have daily information about their payments. Suppose we have information of 1 year leading up to today which is 08/March/2019, I would want to calculate the number of times he/she overpaid in last 1 week. I have used mysql window function but for some reason it does not seem to work

@GMB A sample data would look like this:Suppose for this account we have info from last march 2018. I just want the number of times paid_status = overpaid from the last date that I have on my file which is of today - 08/March/2019 and previous 7 days, 14 days, 1 month or any duration of my choosing. Your query will hardcode it only for 7 days.

ACCOUNT_ID paid_status amt dte
-----------------------
1234 overpaid 100 01/March/2018
.
.
.
1234 overpaid 120 01/March/2019
1234 not paid 0 02/March/2019
1234 overpaid 110 03/March/2019
1234 overpaid 120 04/March/2019
1234 overpaid 130 05/March/2019
1234 overpaid 120 06/March/2019
1234 overpaid 120 07/March/2019
1234 overpaid 121 08/March/2019

Query:

,COUNT(CASE WHEN paid_status = 'OVERPAID' THEN 1 END)
 over (PARTITION BY ACCOUNT_ID
 ORDER BY DTE ROWS BETWEEN 7 PRECEDING AND UNBOUNDED FOLLOWING
 ) AS num_times_overpaid_week1

The output should be like this(not including today's info):

account_id num_times_overpaid_week1
1234 6

While I am getting multiple rows for the same account_id and it is not exactly calulating the field correctly

From your sample data it seems like you are looking for a simple aggregated query (no need for window functions):

SELECT account_id, SUM(paid_status = 'OVERPAID') AS num_times_overpaid_week1
FROM mytable
WHERE dte >= CURRENT_DATE - INTERVAL 7 DAY
GROUP BY account_id

Expression SUM(paid_status = 'OVERPAID') uses a nice MySQL feature where conditions return 1 when satisfied and 0 when not.

NB: if, for some reason, you do want to use window functions (maybe to perform other computation), then you would need to use ROW_NUMBER() to rank records by date, and the filter out only the most recent record per account in an outer query. I think that the definition of the window can be largely simplified:

SELECT *
FROM (
 SELECT 
 account_id, 
 SUM(paid_status = 'OVERPAID') OVER(PARTITION BY account_id) AS num_times_overpaid_week1,
 -- possibly other columns
 ROW_NUMBER() OVER(PARTITION BY account_id ORDER BY dte DESC) rn
 FROM mytable
 WHERE dte >= CURRENT_DATE - INTERVAL 7 DAY
) x WHERE rn = 1