Geek Answers Handbook

Get all the great users - MySql

I have a table containing the following type and information.

product_id | user_id | product | date_opened
1          |   10    |   shoes |  2016-04-01
2          |   26    |   shoes |  2016-04-01
3          |   10    |   watch |  2016-04-01
4          |   23    |   shoes |  2016-04-01
5          |   10    |   shoes |  2016-01-01
6          |   13    |   watch |  2016-01-01
7          |   14    |   shoes |  2015-11-02
8          |   10    | slippers|  2015-11-02
9          |   10    |   shoes |  2015-11-02
10         |   15    |   watch |  2015-11-02
11         |   19    |   watch |  2015-09-03
12         |   19    |   watch |  2015-03-02
13         |   19    |   shoes |  2015-01-03

Users can buy products if they are open. date_openare date cycles. users can buy as many products as they want, for example user_id 10 , user 10 has 2 products in the cycle 2016-04-01. One product on 2016-01-01, two on 2015-11-02.

Now I would like to get all (individual) user_id that are active / have products in all the previous three cycles (2016-04-01, 2016-01-01 and 2015-11-02).

Please note that a user may have many products in a cycle.

: - 3 dates . 2016-04-01 2016-01-01 2015-11-02. 1 2 .

: . (2000-04-05, 2001-09-03, 2006-09-01, 2015-11-02 ..)..) ('2016-04-01', '2016-01-01', '2015-11-02').

+4
2

SQL , :

SELECT USER_ID
FROM   YOUR_TABLE OUTER_TABLE
WHERE  3 = (SELECT COUNT(DISTINCT DATE_OPENED)
            FROM   YOUR_TABLE INNER_TABLE
            WHERE  DATE_OPENED IN ('2016-04-01', '2016-01-01', '2015-11-02')
            AND    OUTER_TABLE.USER_ID = INNER_TABLE.USER_ID);

(), :

SELECT DISTINCT USER_ID
FROM   YOUR_TABLE OUTER_TABLE
WHERE EXISTS (SELECT 1
              FROM   YOUR_TABLE INNER_TABLE
              WHERE  OUTER_TABLE.USER_ID = INNER_TABLE.USER_ID
              WHERE  DATE_OPENED = '2016-04-01')
AND EXISTS   (SELECT 1
              FROM   YOUR_TABLE INNER_TABLE
              WHERE  OUTER_TABLE.USER_ID = INNER_TABLE.USER_ID
              WHERE  DATE_OPENED = '2016-01-01')
AND EXISTS   (SELECT 1
              FROM   YOUR_TABLE INNER_TABLE
              WHERE  OUTER_TABLE.USER_ID = INNER_TABLE.USER_ID
              WHERE  DATE_OPENED = '2015-11-02');
+3
select distinct (user_id) from prod inner join
(select distinct(date_opened) from prod order by date_opened desc limit 3) t 
on prod.date_opened = t.date_opened

3 . user_id-s

SqlFiddle

+1

More articles:


All Articles