4

I have a table contains 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 it is open. date_open is the date cycle. users can buy as many product as he wants, example is user_id 10, user 10 have 2 products on cycle 2016-04-01. One product on 2016-01-01, two on 2015-11-02.

Now, I would like to get all the (distinct) user_id who are active / have products on all previous 3 cycle dates (2016-04-01, 2016-01-01 and 2015-11-02).

Note that a user can have many products in a cycle.

Additional: Active - should consecutively have products in 3 set dates and without skipping. So it should show all user with products in date 2016-04-01 and 2016-01-01 and 2015-11-02. Not user with products only in 1 or 2 dates.

Another problem here: Example, I have users with different dates (2000-04-05 , 2001-09-03, 2006-09-01, 2015-11-02 and so on..) I just want to get here are all those user with dates ('2016-04-01', '2016-01-01', '2015-11-02').

c.k
  • 1,075
  • 1
  • 18
  • 35
  • Use `GROUP BY` while reading [this topic](http://stackoverflow.com/questions/164319/is-there-any-difference-between-group-by-and-distinct) and [the group by manual page](https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html) – Martin Jun 22 '16 at 07:08

2 Answers2

3

This SQL gets you the distinct users that have products on all previous three cycle dates:

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);

The statement may be faster (untested) if it is rewritten as follows:

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');
Samuel Renold
  • 302
  • 1
  • 8
  • your dates are not dynamic here – techie_28 Jun 21 '16 at 07:25
  • As far as I understood, they do not need to be dynamic. The requirement is to get a list of active users, i.e. users that have products on the three previous cycle dates. Of course, the SQL is different if the requirement is to get a list of users that have products on at least three different cycle dates in the past. – Samuel Renold Jun 21 '16 at 07:31
  • what happens when date ticks to `2017`? – techie_28 Jun 21 '16 at 07:32
  • How the last three cycle dates are being evaluated is not at the core of the question. In my answer, I assume that the last three cycle dates are those listed in the question. – Samuel Renold Jun 21 '16 at 07:38
  • @c.k do you not need the years to be dynamic here?This query wont be relevant in 2017 – techie_28 Jun 21 '16 at 09:33
  • @techie_28 No need to be dynamic. – c.k Jun 22 '16 at 05:00
  • @SamuelRenold, Just found out that it will also get all the user even he had only product in 2016-04-01. It should display users with products on '2016-04-01' **AND** '2016-01-01' **AND** '2015-11-02' – c.k Jun 22 '16 at 05:17
  • @c.k `IN(..)` is just like the logical `OR` I believe you should extract the year from the date and then check using `&&` operator check this http://stackoverflow.com/questions/8712771/mysql-extract-year-from-date-format – techie_28 Jun 22 '16 at 05:50
  • @SamuelRenold, Thank you. It is working well. Just too slow when I run the query. – c.k Jun 23 '16 at 08:30
  • By the way what do you call the outer and inner tables? Temporary tables? Is there a way too make this query faster? – c.k Jun 23 '16 at 08:43
  • The select which counts the number of distinct dates is called a sub select. How many records does your table have? I have added an untested variant of the query that may be faster. – Samuel Renold Jun 27 '16 at 10:27
1
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

Inner select gets the last 3 cycles. Outer select finds all records for that cycles and gets distinct user_id-s from them

SqlFiddle

Ruslan Bes
  • 2,715
  • 2
  • 25
  • 32
  • I like the idea here. Problem is, if some of my users have dates that are not listed on my question `('2016-04-01', '2016-01-01', '2015-11-02')`. Example, I have users with different dates `(2000-04-05 , 2001-09-03, 2006-09-01, 2015-11-02 and so on..)` So he will be included using your query. I just want to get are all those user with dates `('2016-04-01', '2016-01-01', '2015-11-02')`. Hope to hear from you. Thanks! – c.k Jun 22 '16 at 06:42
  • Users who have dates in older cycles and also in last 3 cycles will be included, users who have dates ONLY in older cycles - will not, [sqlfiddle](http://sqlfiddle.com/#!9/eb990/1). If you want to get users who ONLY have dates in last 3 cycles and in no other cycles, please mention that and I'll correct my query a little bit – Ruslan Bes Jun 22 '16 at 07:35
  • I just updated my post. And some sample on the table added. – c.k Jun 23 '16 at 01:34