-3

I need to get How many days each customer place order?

Table name - T_Order

OrderId    OrderDate    MenuItemId  Quantity    CustomerId
    1       2021-01-02    1            2            1
    2       2021-01-02    3            1            1
    3       2021-01-02    5            1            1
    4       2021-01-15    1            3            3
    5       2021-01-15    3            2            3
    6       2021-01-20    2            1            4
    7       2021-01-26    4            5            5
    8       2021-01-26    5            5            5
    9       2021-02-02    1            2            1
    10      2021-02-02    3            2            1
    11      2021-02-15    2            1            3
    12      2021-02-15    3            1            3
    13      2021-02-25    2            1            4
    14      2021-02-25    3            1            4
    15      2021-02-28    1            2            5
    16      2021-03-06    2            1            1
    17      2021-03-06    3            1            1
    18      2021-03-10    4            3            3
    19      2021-03-10    5            3            3
    20      2021-03-18    2            1            1
    21      2021-03-21    4            1            3
    22      2021-03-21    5            1            3

Table name - T_Menu

MenuId  Items             Price
1       Pizza             2500.00
2       Pasta             1000.00
3       Garlic Bread      800.00
4       Burgers           900.00
5       Cheesecake        500.00

Table name - T-Customer

CustomerId  FirstName   
1             Steve 
3             Jon   
4             Lisa  
5             Robert    


Note: If the total bill is more than Rs.3000 they offer a 5% discount for each customer. Further if the order placed by a loyalty card holder 10% discount will be given for the total bill.

Also I need answers for the following questions.

1. what is the most purchased item for each customer?
2. When was the first order date for each customer?
3. Find jim's average income in March?
4. John wants to see a report on every price change for menu items. How you can fulfill this requirement?
1
  • Please only ask a single question at a time. If you need to, create separate questions, which references to this question if you need to provide context.
    – Hannah Vernon
    Oct 20 at 13:51
1

The reason why you can see incorect result is that COUNT(o.OrderDate) returns number of rows where o.OrderDate is not null. While you are looking for distinct values. You may use code as per below, but I think that there should be an easier way (with one of the window functions)

with CTE AS 
(
SELECT Distinct c.FirstName as FirstName, 
                o.OrderDate as date,
                c.CustomerId
FROM T_Orders o
INNER JOIN T_Customer c
ON c.CustomerId = o.CustomerId
)
select max(FirstName), count(OrderDate)
from CTE 
GROUP BY c.CustomerId
0
1

The issue you're running into is you have the same CustomerId ordering on the same OrderDate multiple times, so the COUNT() function will be inclusive of dupe dates.

You can simply use the DISTINCT keyword inside the aggregate COUNT() method to minimally change your existing query like so:

SELECT MAX(c.FirstName) as FirstName, COUNT(DISTINCT o.OrderDate) as Days
FROM T_Orders o
INNER JOIN T_Customer c
    ON c.CustomerId = o.CustomerId
GROUP BY c.CustomerId

Also, you don't have to use an aggregate function on the FirstName field since it'll always be the same value within your existing CustomerId grouping (i.e. each CustomerId will always have 1 distinct FirstName). Rather you can add it to your GROUP BY clause, and it should not change the output of your results, like so:

SELECT c.FirstName, COUNT(DISTINCT o.OrderDate) as Days
FROM T_Orders o
INNER JOIN T_Customer c
    ON c.CustomerId = o.CustomerId
GROUP BY c.CustomerId, c.FirstName
0

Not the answer you're looking for? Browse other questions tagged or ask your own question.