Mysql – Solving MySQL Query

MySQL

I am trying to solve the following query with these data:
Some assumptions will need to be made. Please list any such assumptions
Users:

+---------+---------+--------+-----+--------+
| USER_ID |  EMAIL  | GENDER | AGE |  NAME  |
+---------+---------+--------+-----+--------+
| 1       | a@a.com | m      | 30  | rob    |
| 2       | a@a.com | m      | 31  | robert |
| 3       | b@b.com | f      | 18  | lucie  |
| 4       | b@b.com | f      | 22  | lulu   |
| 5       | c@c.com | m      | 10  | kim    |
| 6       | c@c.com | f      | 18  | kim    |
| 7       | c@c.com | f      | 08  | kim    |
| 8       | d@d.com | f      | 18  | JJ     |
| 9       | d@d.com | m      | 22  | Jay    |
| 10      | e@e.com | f      | 88  | Bill   |
| 11      | e@e.com | f      | 88  | Will   |
| 12      | e@e.com | f      | 60  | Will   |
| 13      | f@f.com | m      | 70  | George |
+---------+---------+--------+-----+--------+

Subscriptions:

+-----------------+---------+------------------+------------------+
| SUBSCRIPTION_ID | USER_ID | SUBSCIPTION_TYPE | ACTIVE_INDICATOR |
+-----------------+---------+------------------+------------------+
| 1               | 2       | Magazine         | Yes              |
| 2               | 3       | Music            | CD               |
| 3               | 3       | Magazine         | Yes              |
| 4               | 3       | Video            | Yes              |
| 5               | 8       | Magazine         | Yes              |
| 6               | 9       | Video            | Yes              |
| 7               | 10      | Magazine         | No               |
| 8               | 13      | Magazine         | yes              |
+-----------------+---------+------------------+------------------+

Transactions: 

+-----------------+--------------+-------------+
| SUBSCRIPTION_ID |    ACTION    |  TIMESTAMP  |
+-----------------+--------------+-------------+
| 1               | Renewal      | 2002-sep-10 |
| 2               | Renewal      | 2002-Jan-01 |
| 2               | Cancellation | 2002-Feb-01 |
| 3               | Renewal      | 2002-Aug-20 |
| 4               | Renewal      | 2002-Aug-01 |
| 4               | Renewal      | 2002-Sep-01 |
| 5               | Renewal      | 2002-Aug-01 |
| 6               | Renewal      | 2001-Sep-01 |
| 7               | Renewal      | 2002-Sep-01 |
| 7               | Cancellation | 2002-Sep-10 |
+-----------------+--------------+-------------+

Assumptions:

  1. generate a list of unique email addresses with the latest name, gender and age for a user with that email.

  2. The selection criteria limit the list to users which never subscribed to anything; or; users with inactive subscriptions; or; users with active subscriptions that renewed between Sep 1st and Sep 30th of any year.

The answer should be:

+---------+--------+-----+--------+
|  EMAIL  | GENDER | AGE |  NAME  |
+---------+--------+-----+--------+
| a@a.com | m      | 31  | robert |
| b@b.com | f      | 22  | lulu   |
| c@c.com | f      | 08  | kim    |
| d@d.com | m      | 22  | Jay    |
| e@e.com | f      | 60  | Will   |
+---------+--------+-----+--------+

I am trying this query:

select u.user_id, email, gender, age, name
from users u 
    left join subscriptions s on u.user_id = s.user_id
where s.user_id is null
    or s.active_indicator='No'
    or u.user_id in 
    (
        select u.user_id
        from subscriptions s,transactions t, users u 
        where u.user_id = s.user_id
            and s.subscription_id = t.subscription_id
            and t.`action`='Renewal' 
            and s.active_indicator='Yes'
            and month(timestamp) = 9 
    )

which shows the following output:

+--------+---------+--------+-----+------+
| USERID |  EMAIL  | GENDER | AGE | NAME |
+--------+---------+--------+-----+------+
| 1      | a@a.com | m      | 30  | rob  |
| 4      | b@b.com | f      | 22  | lulu |
| 5      | c@c.com | m      | 10  | kim  |
| 6      | c@c.com | f      | 18  | kim  |
| 7      | c@c.com | f      | 8   | kim  |
| 11     | e@e.com | f      | 88  | will |
| 12     | e@e.com | f      | 60  | will |
+--------+---------+--------+-----+------+

Any ideas? I am a bit lost, the main problem I am having is that I cannot sort the list before the group by.

Best Answer

      ( SELECT  u.email, u.gender, u.age, u.name, 'never subscribed to anything' AS 'State'
            FROM  Users AS u
            LEFT JOIN  Subscriptions AS s ON s.user_id = u.user_id
            WHERE  s.subscription_id IS NULL 
      )
    UNION  DISTINCT 
      ( SELECT  u.email, u.gender, u.age, u.name, 'inactive subscription' AS 'State'
            FROM  Users AS u
            JOIN  Subscriptions AS s ON s.user_id = u.user_id
            WHERE  s.active_indicator != 'Yes' 
      )
    UNION  DISTINCT 
      ( SELECT  u.email, u.gender, u.age, u.name, 'active
              and  Sep. renewal'
            FROM  Users AS u
            JOIN  Subscriptions AS s ON s.user_id = u.user_id
            JOIN  Transactions AS t ON t.subscription_id = s.subscription_id
            WHERE  s.active_indicator = 'Yes'
              AND  MID(t.timestamp, 5, 2) = '09' 
      )
    ORDER BY  email;

Notice how closely this parallels your specifications. ('or' becomes UNION DISTINCT.)

This formulation requires TIMESTAMP to be a DATETIME or TIMESTAMP, not VARCHAR. If you want the current output (yyyy-MMM-dd), use DATE_FORMAT when displaying.

Related Question