Share via

Methods on Approaching SQL Queries

Carlton Patterson 761 Reputation points
2021-01-06T12:18:51.657+00:00

Hello Community,

I wasnt quite sure how to phrase the title of this question, because my main point of this question is not simply to find the question to the query that I'm about post, but how to approach the answer to query.

The query is a follows:

Find the customer with the highest total order cost between 2019-02-01 to 2019-05-01. Output their first name, total cost of their items, and the date.

The tables are as follows: 

CREATE TABLE orders (
    id int,
    cust_id int,
    order_date date,
    order_quantity int,
    order_details nvarchar(50),
    order_cost int)

INSERT orders VALUES
(1,3,CONVERT(DATETIME, '2019-03-04', 120),4,N'Coat',100),
(2,3,CONVERT(DATETIME, '2019-03-01', 120),1,N'Shoes',80),
(3,3,CONVERT(DATETIME, '2019-03-04', 120),1,N'Skirt',30),
(4,7,CONVERT(DATETIME, '2019-02-01', 120),1,N'Coat',100),
(5,7,CONVERT(DATETIME, '2019-03-10', 120),1,N'Shoes',80),
(6,15,CONVERT(DATETIME, '2019-02-01', 120),2,N'Boats',100),
(7,15,CONVERT(DATETIME, '2019-01-11', 120),3,N'Shirts',60),
(8,15,CONVERT(DATETIME, '2019-03-11', 120),1,N'Slipper',20),
(9,15,CONVERT(DATETIME, '2019-03-01', 120),2,N'Jeans',80),
(10,15,CONVERT(DATETIME, '2019-03-09', 120),3,N'Shirts',50),
(11,5,CONVERT(DATETIME, '2019-02-01', 120),1,N'Shoes',80),
(12,12,CONVERT(DATETIME, '2019-01-11', 120),3,N'Shirts',60),
(13,12,CONVERT(DATETIME, '2019-03-11', 120),1,N'Slipper',20),
(14,4,CONVERT(DATETIME, '2019-02-01', 120),1,N'Shoes',80),
(15,4,CONVERT(DATETIME, '2019-01-11', 120),3,N'Shirts',60),
(16,3,CONVERT(DATETIME, '2019-04-19', 120),3,N'Shirts',50),
(17,7,CONVERT(DATETIME, '2019-04-19', 120),1,N'Suit',150),
(18,15,CONVERT(DATETIME, '2019-04-19', 120),1,N'Skirt',30),
(19,15,CONVERT(DATETIME, '2019-04-19', 120),2,N'Dresses',200),
(20,12,CONVERT(DATETIME, '2019-01-11', 120),5,N'Coat',20),
(21,7,CONVERT(DATETIME, '2019-04-01', 120),1,N'Suit',50),
(22,7,CONVERT(DATETIME, '2019-04-02', 120),1,N'Skirt',30),
(23,7,CONVERT(DATETIME, '2019-04-03', 120),1,N'Dresses',50),
(24,7,CONVERT(DATETIME, '2019-04-04', 120),1,N'Coat',25)

SELECT * FROM orders


CREATE TABLE customers (
    id int,
    first_name nvarchar(50),
    last_name nvarchar(50),
    city nvarchar(50),
    address nvarchar(50),
    phone_number nvarchar(50))

INSERT customers VALUES
(8,N'John',N'Joseph',N'Arizona',N'',N'928-386-8164'),
(7,N'Jill',N'Michael',N'Florida',N'',N'813-297-0692'),
(4,N'William',N'Daniel',N'Colorado',N'',N'813-368-1200'),
(5,N'Henry',N'Jackson',N'Hawaii',N'',N'808-601-7513'),
(13,N'Emma',N'Isaac',N'Hawaii',N'',N'808-690-5201'),
(14,N'Liam',N'Samuel',N'Hawaii',N'',N'808-555-5201'),
(15,N'Mia',N'Owen',N'Hawaii',N'',N'808-640-5201'),
(1,N'Mark',N'Thomas',N'Arizona',N'4476 Parkway Drive',N'602-993-5916'),
(12,N'Eva',N'Lucas',N'Arizona',N'4379 Skips Lane',N'301-509-8805'),
(6,N'Jack',N'Aiden',N'Arizona',N'4833 Coplin Avenue',N'480-303-1527'),
(2,N'Mona',N'Adrian',N'California',N'1958 Peck Court',N'714-409-9432'),
(10,N'Lili',N'Oliver',N'California',N'3832 Euclid Avenue',N'530-695-1180'),
(3,N'Farida',N'Joseph',N'Florida',N'3153 Rhapsody Street',N'813-368-1200'),
(9,N'Justin',N'Alexander',N'Colorado',N'4470 McKinley Avenue',N'970-433-7589'),
(11,N'Frank',N'Jacob',N'Hawaii',N'1299 Randall Drive',N'808-590-5201')

SELECT * FROM customers

As you've probably guess by now, I have a rudimentary understanding of T-SQL, and while I can often find the answer to simple queries, this query requires deep thought, and I would like someone to demonstrate how they approach solving this query?

Let me know if you would like me to post the suggested answer to the query, but I would welcome your suggestions before I post the answer

Thanks in advance

Developer technologies | Transact-SQL
Accepted answer
  1. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2021-01-06T22:13:43.513+00:00

    . Would you have been able to determine the answer from just the wording

    Not really, since the wording is unclear.

    1. What additional information would you need to figure out the answer?

    In a real-world scenario - I would talk to the person who wrote the spec to get clarification.

    So this is why these kind of schoolbook exercises are more difficult than real-life problems. There is often no one to ask. So you may produce one query - but it is far away from the intended one, because you interpreted the specification differently.

    1. How would you go about breaking down the solution to figure out the answer?

    This is what I outlined in my first post, and Tom's post follows the same lines. And we both got stuck on the date which did not really fit with our interpretation.

    1 person found this answer helpful.
    0 comments

7 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2021-01-06T13:35:34.14+00:00

    Find the customer with the highest total order cost between 2019-02-01 to 2019-05-01.

    So you need to compute the total order cost for all customers in this period. I believe that you can write this query.

    Then you need to find the top customer in the pack. This can be done in more than one way, but on SQL Server, the easiest way is to use TOP.

    Output their first name, total cost of their items, and the date.

    And once you have the customer, you can join to the Customers table o show the first name. Except that I don't know what "the date" refers to here.

    Or do they mean the customer for the order with the highest cost? I would interpret "customer with the highest total order cost" as the total of all orders for a customer.

    And there is a bit of real-life experience here: you cannot write queries if you don't know the exact requirements.

  2. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2021-01-06T16:32:32.397+00:00

    I was looking for an approach that I professional SQL coder would take to tackle the query.

    And that is what I tried to describe. Including the "what do they mean" thing.

    The suggested solution is nothing I would write, but it is one that would run on about any engine that pretends to support SQL. The solution with TOP I suggested is proprietary to SQL Server.

    It looks funny with the LEFT JOIN to customers, though. One would like think that orders.cust_id does not permit NULL. And if there are orders with out customers and we are supposed to show the customer with the highest cost, why would we care about the customer-less orders?

    I also note that to get the actual cost of the order, we should apparently multiply order_quantity with order_cost. That's, hm, intuitive.

    Finally, when adding code, use the button with ones and zeroes on to avoid accidents. (This is not really a WYSIGWIG editor, but it uses Markdown, a lightweight markup language.)

    0 comments
  3. Carlton Patterson 761 Reputation points
    2021-01-06T16:33:11.923+00:00

    My question to anybody is

    1. Would you have been able to determine the answer from just the wording?
    2. What additional information would you need to figure out the answer?
    3. How would you go about breaking down the solution to figure out the answer?

    There are lot of questions here, (and I have probably a few more questions).

    0 comments
  4. Carlton Patterson 761 Reputation points
    2021-01-06T17:05:56.917+00:00

    ok, I give up.

    No matter what I try, I can't get my query to work. Can someone please take a look at my query and let me know where to tweak it to make it work 

    SELECT
  SubQuery.first_name
 ,SubQuery.order_date
 ,MAX(SubQuery.total_order_cost) AS New_Total_Order
FROM (SELECT
    customers.first_name
   ,orders.order_date
   ,SUM(orders.order_quantity * orders.order_cost) AS total_order_cost
  FROM dbo.customers
  INNER JOIN dbo.orders
    ON customers.id = orders.cust_id
  WHERE orders.order_date BETWEEN '2019-02-01' AND '2019-05-01'
  GROUP BY customers.first_name
          ,orders.order_date) SubQuery
GROUP BY SubQuery.first_name
        ,SubQuery.order_date
        ,SubQuery.total_order_cost
HAVING SubQuery.total_order_cost = MAX(SubQuery.total_order_cost)
    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.