Table "users":

Table "oxygen_point_earns":

I have two tables:

• "users", which stores users basic information
• "oxygen_point_earns", which stores oxygen points earned by specific users

The "users" table has 12 rows, though the "oxygen_point_earns" table contains 13 records, which means that one user can win points even more than once.

I was trying to made some calculation between those tables (e.g. dividing the total of weekly gained points by the weekly users cumulative sum, for each user). The problem occurs when I attempt to get the users cumulative sum.

SELECT STR_TO_DATE(CONCAT(YEARWEEK(op.created_at), ' Sunday'), '%X%V %W') AS week,
       SUM(COUNT(*)) OVER(ORDER BY MIN(op.created_at)) AS user_count,
       SUM(op.oxygen_point)                            AS op_weekly
FROM      users us
LEFT JOIN oxygen_point_earns op 
       ON us.id = op.user_id
GROUP  BY week
ORDER  BY week 

This query gets me the following output:

As you can see, even though the points are correctly computed, the total user count is wrong at the second row: it should be 12 instead of 13 (First week I got 6 users then next week 6 more users registered. So my total user count is 12. On second row I should get 12.)

I tried DISTINCT, GROUP_CONCAT but didn't work. How can I fix this query to get true result of users counts?

CodePudding user response：

One straightforward option is to separate the two operations (aggregation and windowing) using a subquery/cte:

WITH cte AS (
    SELECT STR_TO_DATE(CONCAT(YEARWEEK(op.created_at), ' Sunday'), '%X%V %W') AS week,
           COUNT(DISTINCT user_id)                                            AS cnt,
           SUM(op.oxygen_point)                                               AS op_weekly
    FROM      users us
    LEFT JOIN oxygen_point_earns op ON us.id = op.user_id
    GROUP  BY week
)
SELECT week,
       SUM(cnt) OVER(ORDER BY week)  AS user_count,
       op_weekly
FROM cte
ORDER BY week