CODEWITHSUNDEEP
mysqlsql
Michi
Michi

Reputation: 5471

Count order date if it is the first order of the customer

DB-Fiddle:

/* table sales */
CREATE TABLE sales (
    id int auto_increment primary key,
    customerID VARCHAR(255),
    order_date DATE
);

INSERT INTO sales
(customerID, order_date
)
VALUES 
("Customer_01", "2020-03-15"),
("Customer_01", "2020-03-20"),
("Customer_02", "2020-06-16"),
("Customer_03", "2020-07-27"),
("Customer_03", "2020-08-05"),
("Customer_04", "2020-08-10"),
("Customer_04", "2021-03-14");


/* table customers */
CREATE TABLE customers (
    id int auto_increment primary key,
    customerID VARCHAR(255),
    first_order DATE
);

INSERT INTO customers
(customerID, first_order
)
VALUES 
("Customer_01", "2020-03-15"),
("Customer_02", "2020-06-16"),
("Customer_03", "2020-07-27"),
("Customer_04", "2020-08-10");

Expected Result:

customerID      order_date     first_order    COUNT(distinct s.customerID)
Customer_01     2020-03-15     2020-03-15               1
Customer_01     2020-03-20     2020-03-15               0
Customer_02     2020-06-16     2020-06-16               1
Customer_03     2020-07-27     2020-07-27               1
Customer_03     2020-08-05     2020-07-27               0
Customer_04     2020-08-10     2020-08-10               1

My database consists of two tables sales and customers.
In the customers table you can finde the date of the first_order per customers.
In the sales table you can find each order a customer has done so far.

Now, I want to create a list with all order_dates and in case the order_date was a first_order the COUNT should be 1.
Otherwise the COUNT should be 0.

So far, I came to this query:

SELECT
s.customerID,
s.order_date,
c.first_order,
COUNT(distinct s.customerID)
FROM sales s
JOIN customers c ON c.customerID = s.customerID
WHERE order_date BETWEEN "2020-01-01" AND "2020-12-31"
AND first_order BETWEEN "2020-01-01" AND "2020-12-31"
GROUP BY 1,2,3;

Do you have any idea how I have to modify it to get the expected result?

Upvotes: 0

Views: 84

Answers (4)

Neville Kuyt
Neville Kuyt

Reputation: 29619

Your "count" is a little confusing - but this version is another option:

SELECT
s.customerID,
s.order_date,
c.first_order,
CASE
  when c.first_order = order_date then 1
  else 0
end 
FROM sales s
JOIN customers c ON c.customerID = s.customerID
WHERE order_date BETWEEN "2020-01-01" AND "2020-12-31"
AND first_order BETWEEN "2020-01-01" AND "2020-12-31"
GROUP BY 1,2,3;

Upvotes: 1

forpas
forpas

Reputation: 164089

In your query you can sum the times the order_date is equal to the first_order:

SELECT
  s.customerID,
  s.order_date,
  c.first_order,
  SUM(s.order_date = c.first_order) total
FROM sales s JOIN customers c 
ON c.customerID = s.customerID
WHERE order_date BETWEEN "2020-01-01" AND "2020-12-31"
  AND first_order BETWEEN "2020-01-01" AND "2020-12-31"
GROUP BY s.customerID, s.order_date, c.first_order;

See the demo.
Results:

> customerID  | order_date | first_order | total
> :---------- | :--------- | :---------- | ----:
> Customer_01 | 2020-03-15 | 2020-03-15  |     1
> Customer_01 | 2020-03-20 | 2020-03-15  |     0
> Customer_02 | 2020-06-16 | 2020-06-16  |     1
> Customer_03 | 2020-07-27 | 2020-07-27  |     1
> Customer_03 | 2020-08-05 | 2020-07-27  |     0
> Customer_04 | 2020-08-10 | 2020-08-10  |     1

Upvotes: 1

nbk
nbk

Reputation: 49373

You can use an IF Clause to check the dates

SELECT
s.customerID,
s.order_date,
c.first_order,
IF(s.order_date = c.first_order,1,0 )
FROM sales s
JOIN customers c ON c.customerID = s.customerID
WHERE order_date BETWEEN "2020-01-01" AND "2020-12-31"
AND first_order BETWEEN "2020-01-01" AND "2020-12-31"
;

customerID  | order_date | first_order | IF(s.order_date = c.first_order,1,0 )
:---------- | :--------- | :---------- | ------------------------------------:
Customer_01 | 2020-03-15 | 2020-03-15  |                                     1
Customer_01 | 2020-03-20 | 2020-03-15  |                                     0
Customer_02 | 2020-06-16 | 2020-06-16  |                                     1
Customer_03 | 2020-07-27 | 2020-07-27  |                                     1
Customer_03 | 2020-08-05 | 2020-07-27  |                                     0
Customer_04 | 2020-08-10 | 2020-08-10  |                                     1

db<>fiddle here

Upvotes: 1

GMB
GMB

Reputation: 222432

This looks like a join and conditional logic. I don’t see why you would need aggregation here:

select 
    s.customerID,
    s.order_date,
    c.first_order,
    (s.order_date = c.first_order) is_first_order
from sales s
inner join customers c on c.customerID = s.customerID

Upvotes: 0

Related Questions