Question 176303 in Laravel Question posted in
The official Laravel Documentation can be found here.

Group users by cumulative summation using Laravel Eloquent?

Let’s say I have a MySQL table called points with 4 columns (id, user_id, point, date). And, let’s consider these dummy rows for better understanding.

id user_id point date
1 1 2 2000-01-01
2 1 3 2000-01-01
3 2 5 2000-01-01
4 2 1 2000-01-01
5 1 6 2000-01-02
6 2 2 2000-01-02
7 1 3 2000-01-03
8 2 6 2000-01-03
9 1 1 2000-01-04
10 1 9 2000-01-04
11 2 6 2000-01-04
12 1 7 2000-01-05
13 2 4 2000-01-05
14 1 1 2000-01-06
15 2 6 2000-01-06

I have two variables consecutive_days_to_achieve_reward=2 and per_day_min_points_to_achieve_reward=5. Now, I want to know how many times the users have passed this specific requirement.

So, the result for the above table would be:

user_id times
1 2 2000-01-01 - 2000-01-02 2000-01-04 - 2000-01-05
2 1 2000-01-03 - 2000-01-04

I am getting no clue how to solve MySQL problems with laravel eloquent dealing with cumulative summation. Could someone assist me?

Tags:

2

Answers


  1. 0

    Not sure that I understand entirely. The query below does calculate the days that satisfy that requirement, but I am not sure what you are wanting for ‘times’ value ? It sounds like you want the number of groups of 2 or more days and not the absolute number of days there, although you could probably modify the query a bit to get that.

    As for Laravel eloquent or the Query Builder you should be able to translate a working query to that format, but if you are not too familiar with Laravel I would get a working MySQL query first.

    SELECT user_id, COUNT(*) AS times_passed_requirement,
       GROUP_CONCAT(date ORDER BY date) AS satisfied_dates
FROM (
    SELECT t1.user_id, t1.date,
        CASE
            WHEN EXISTS (
                SELECT *
                FROM (
                    SELECT user_id, date, SUM(point) AS total_points
                    FROM points
                    GROUP BY user_id, date
                ) AS t2
                WHERE t2.user_id = t1.user_id
                    AND (t2.date = DATE_ADD(t1.date, INTERVAL 1 DAY) OR t2.date = DATE_SUB(t1.date, INTERVAL 1 DAY))
                    AND t2.total_points >= 5
            ) THEN 1
            ELSE 0
        END AS requirement_passed
    FROM (
        SELECT user_id, date, SUM(point) AS total_points
        FROM points
        GROUP BY user_id, date
    ) AS t1
    WHERE t1.total_points >= 5
) AS subquery
WHERE requirement_passed = 1
GROUP BY user_id;

    I at least ran your data through and it seems to give the correct result.

    user_id passed dates
1      4       2000-01-01,2000-01-02,2000-01-04,2000-01-05
2      2       2000-01-03,2000-01-04
    Login or Signup to reply.
  2. 0

    I would start with the following (replacing the user-defined variables with params):

    SET @consecutive_days_to_achieve_reward = 2,
    @per_day_min_points_to_achieve_reward = 5;

WITH t1 (user_id, date, point, grp) AS (
    SELECT user_id, date, SUM(point) point, date - INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date ASC) DAY
    FROM points
    GROUP BY user_id, date
    HAVING point >= @per_day_min_points_to_achieve_reward
),
t2 (user_id, consec, min_date, max_date) AS (
    SELECT user_id, COUNT(date) consec, MIN(date), MAX(date)
    FROM t1
    GROUP BY user_id, grp
    HAVING consec >= @consecutive_days_to_achieve_reward
)
SELECT
    user_id,
    SUM(FLOOR(consec/@consecutive_days_to_achieve_reward)) times,
    GROUP_CONCAT(min_date, ' - ', max_date SEPARATOR ', ') date_ranges
FROM t2
GROUP BY user_id;

    Output:

    user_id times date_ranges
    1 2 2000-01-01 – 2000-01-02, 2000-01-04 – 2000-01-05
    2 1 2000-01-03 – 2000-01-04

    The first CTE produces the following output, based on your sample data (rn added to aid understanding):

    user_id date point rn grp
    1 2000-01-01 5 1 1999-12-31
    1 2000-01-02 6 2 1999-12-31
    1 2000-01-04 10 3 2000-01-01
    1 2000-01-05 7 4 2000-01-01
    2 2000-01-01 6 1 1999-12-31
    2 2000-01-03 6 2 2000-01-01
    2 2000-01-04 6 3 2000-01-01
    2 2000-01-06 6 4 2000-01-02

    The second CTE outputs:

    user_id consec min_date max_date
    1 2 2000-01-01 2000-01-02
    1 2 2000-01-04 2000-01-05
    2 2 2000-01-03 2000-01-04

    Although the above can be built up using Laravel’s Query Builder, I would probably pass the parameterized query to DB::select().

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search