MYSQL query with complex aggregated results

Question

Here is the scenario. I've got two tables with schema like this

experience: (id, title)

user_experience: (id, experience_id, created_date)

When a user adds an experience to his to do list, a record is added in user_experience. Now I want to get the dates when an experience achieved a milestone of 2, 5 or 10 listers.

Required result: id (experience.id), title, created_date, count (no of users who listed the experience)

If an experience achieved 10 listers, then the result should include the records corresponding to the times when it got 2 and 5 listers.

I'm using this query for getting required result, and looking for a better "query".

SELECT e.id, e.title, ue_res.created_date, ue_res.count FROM
experience e INNER JOIN
(
    SELECT ue.experience_id, (
        SELECT COUNT(uee.id) FROM user_experience uee WHERE uee.experience_id = ue.experience_id AND uee.created_date <= ue.created_date
    ) AS `count`, ue.created_date FROM user_experience ue
) ue_res
ON e.id = ue_res.experience_id
WHERE ue_res.uecount IN (2,5,10)
ORDER BY e.id,ue_res.created_date ASC

I can't create another table for achievements because the milestones can be changed anytime, so I'm only looking for a query.

Here some sample data

Sample data for experience:

1   |   Bunjee jump from Eifel Tower
2   |   Surf in Rio
3   |   Fly over Grand Canyon
4   |   Go to a Mexican resturant

Sample data for user_experience:

1   |   1   |   2013-03-01
2   |   4   |   2013-03-02
3   |   1   |   2013-03-03
4   |   3   |   2013-03-04
5   |   2   |   2013-03-05
6   |   3   |   2013-03-06
7   |   4   |   2013-03-07
8   |   1   |   2013-03-08
9   |   1   |   2013-03-09
10  |   1   |   2013-03-10
11  |   3   |   2013-03-11
12  |   2   |   2013-03-12
13  |   2   |   2013-03-13

Result for sample data:

1   |   Bunjee jump from Eifel Tower    |   2013-03-03  |   2
3   |   Fly over Grand Canyon           |   2013-03-06  |   2
4   |   Go to a Mexican resturant       |   2013-03-07  |   2
1   |   Bunjee jump from Eifel Tower    |   2013-03-10  |   5
2   |   Surf in Rio                     |   2013-03-12  |   2

Thanks in advance :)

@Adee . . . Your query is fine. MySQL does not support the window functions that you would need for a more concise, pure-SQL solution. — Gordon Linoff, Mar 18 '13 at 15:35

mrras · Accepted Answer · 2013-03-19T07:37:31.627

This query might do the trick:

SET @helper:=0;

SET @oldid:=0;

SELECT ue.experience_id AS id,
       e.title,
       ue.created_date,
       ue.helper        AS milestone
FROM   (SELECT ue.id,
               ue.experience_id,
               ue.created_date,
               CASE @oldid
                 WHEN ue.experience_id THEN ( @helper := @helper + 1 )
                 ELSE ( @helper := 1 )
               end                            AS helper,
               ( @oldid := ue.experience_id ) oldid
        FROM   user_experience ue
        ORDER  BY ue.experience_id ASC,
                  ue.created_date ASC) ue
       JOIN experience e
         ON ( e.id = ue.experience_id )
WHERE  ue.helper IN ( 2, 5, 10 );

Pay attention that the data ordering is what made the using of running variables work.

Now that's exactly what I wanted but didn't succeed with making it. Thanks a lot :) — Adee, Mar 19 '13 at 07:29

MYSQL query with complex aggregated results

1 Answers1