SQL: Select items not bought by customer on daily basis

Ravi

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

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.

edited at
0

Comments

0 comments
Login to comment

Related

Select Products NOT Bought by a Customer

Generate Sequential IDs for Line Items on a Per Customer Basis in SQL Server

SQL - finding customer with 2 specific products bought

SQL list items bought by everyone

How to determine which customer bought the items based on price

SQL query to find which customer bought Glass or at least a Doll

Select all customer who have bought 'x' times something from the category 'y' within the last 'z' days

MySQL Get cumulative customer transactions (not SQL transactions) on a monthly basis

SQL query to find customer that bought all of a type of product and also visited in a certain month

How to export data from Cloud SQL to BigQuery on a daily basis?

Get all customer that bought 2 product

Checking if customer has already bought something in WooCommerce

SQL Counter Select statement on an hourly basis

Daily Inventory from Bought/Sold transaction log

SQL Select first price when grouping by customer

SQL Query to find people who bought greater than 3 items in a where clause

Finding customers that only bought items no one else bought

tracking customer retension on weekly basis

SQL Server - Select daily values for each month

Copying Data from one SQL Server Database to Another SQL Database on a Daily basis

Swing GUI usage on daily basis

Get top record on daily basis

Calculate returns on a daily basis in R

Postgres, Count entries on daily basis

Avg amount of items user bought over the time

Get the most bought top 10 items as a list

Find X items for a user that he has not bought

Customers who bought the exact 2 items

Get the orders IDs related to a product bought by a customer in Woocommerce