I have a table that has daily purchase of a customer. I am trying to get the list of all items that the customer did not purchase on daily basis.
More explanation: Below is the table
+------------+---------+------------+
| dt | product | product_id |
+------------+---------+------------+
| 2019-01-01 | Milk | 1 |
| 2019-01-02 | Bread | 2 |
| 2019-01-03 | Butter | 3 |
| 2019-01-04 | Beer | 4 |
| 2019-01-05 | Salt | 5 |
| 2019-01-06 | Sugar | 6 |
+------------+---------+------------+
I am trying to get a report of Not purchased items based on the whole list.
+------------+------------------------------+
| dt | product_not_purchased |
+------------+------------------------------+
| 2019-01-01 | Bread,Butter,Beer,Salt,Sugar |
| 2019-01-02 | Milk,Butter,Beer,Salt,Sugar |
| 2019-01-03 | Milk,Bread,Beer,Salt,Sugar |
+------------+------------------------------+
For the sake of simplicity the data here is for just one customer and only one item will be purchased by the customer. But the dates are not static, the dates could keep growing with irregular frequency.
Sample query for one day:
select dt, product, product_id from
trans where product_id not in (select distinct product_id
from trans where dt not in ( '2019-01-01'))
where dt = '2019-01-01';
I am trying to extend it for each day. I tried using CTE and partitions but it did not work. The other solution I though was to create a temp table and use minus clause. How should I go about solving this problem? or Is temp table the best solution?
SQL Queries to create table and insert values. I am using MySQL:
create table trans (dt date, product varchar(10), product_id int);
insert into trans values ('2019-01-01','Milk',1);
insert into trans values ('2019-01-02','Bread',2);
insert into trans values ('2019-01-03','Butter',3);
insert into trans values ('2019-01-04','Beer',4);
insert into trans values ('2019-01-05','Salt',5);
insert into trans values ('2019-01-06','Sugar',6);
If you only have the trans
table:
select dt, group_concat(product order by product) product_not_purchased from (
select distinct t.dt, tt.product from trans t
inner join trans tt
on tt.dt <> t.dt
) t
group by dt
See the demo
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments