Skip to main content

SQL Question and Answer - Interview Fresher as well as Experience

CREATE TABLE orders (

    id INT PRIMARY KEY,

    customer_id INT,

    courier_id INT,

    seller_id INT,

    order_timestamp_utc DATETIME,

    amount FLOAT,

    city_id INT

);

INSERT INTO orders (id, customer_id, courier_id, seller_id, order_timestamp_utc, amount, city_id)

VALUES

    (1, 101, 201, 301, '2023-01-15 10:30:00', 50.00, 401),

    (2, 102, 202, 302, '2023-01-16 11:45:00', 75.50, 402),

    (3, 103, 203, 303, '2023-01-17 12:15:00', 120.25, 403),

    (4, 104, 204, 304, '2023-01-18 09:00:00', 30.75, 404),

    (5, 105, 205, 305, '2023-01-19 14:20:00', 90.80, 405);

Q.1 Which hour has the highest average order volume per day? Your output should have the hour which satisfies that condition, and average order volume.

Approach 1: With CTE

WITH RankedHours AS ( SELECT DATEPART(HOUR, order_timestamp_utc) AS hour_of_day, AVG(amount) AS average_order_volume, RANK() OVER (ORDER BY AVG(amount) DESC) AS ranking FROM postmates_orders GROUP BY DATEPART(HOUR, order_timestamp_utc) ) SELECT hour_of_day, average_order_volume

FROM RankedHours WHERE ranking = 1; Approach 2: Without CTE which is Select and From SELECT hour_of_day, average_order_volume FROM ( SELECT DATEPART(HOUR, order_timestamp_utc) AS hour_of_day, AVG(amount) AS average_order_volume, RANK() OVER (ORDER BY AVG(amount) DESC) AS ranking FROM postmates_orders GROUP BY DATEPART(HOUR, order_timestamp_utc) ) Y WHERE ranking = 1; Approach 3: Using the Top approach SELECT DATEPART(HOUR, order_timestamp_utc) AS hour_of_day, AVG(amount) AS average_order_volume FROM postmates_orders GROUP BY DATEPART(HOUR, order_timestamp_utc) HAVING AVG(amount) = ( SELECT TOP 1 AVG(amount) FROM postmates_orders GROUP BY DATEPART(HOUR, order_timestamp_utc) ORDER BY AVG(amount) DESC);

Let's break the the third approach and see how it has been done

First Step: Selecting Hourly Averages

SELECT DATEPART(HOUR, order_timestamp_utc) AS hour_of_day, AVG(amount) AS average_order_volume FROM postmates_orders GROUP BY DATEPART(HOUR, order_timestamp_utc);

Second Step: Subquery to Find Highest Average:

SELECT TOP 1 AVG(amount) FROM postmates_orders GROUP BY DATEPART(HOUR, order_timestamp_utc) ORDER BY AVG(amount) DESC;

Third Step: Main Query with HAVING Clause:

SELECT DATEPART(HOUR, order_timestamp_utc) AS hour_of_day, AVG(amount) AS average_order_volume FROM postmates_orders GROUP BY DATEPART(HOUR, order_timestamp_utc) HAVING AVG(amount) = (Subquery Result);

Link of the Question - https://platform.stratascratch.com/coding/2014-hour-with-the-highest-order-volume?code_type=5



Labels:

Comments

Post a Comment

Popular posts from this blog

My Love for Cycle. :)

  Since my childhood, I used to wish I could have a cycle and would go cycling on Road without any fear.  Well, that came true in the last year, 2021, in October. :) Well, what to say about cycling? It's becoming one of my daily routines; I miss it badly if I do not go out cycling. It gives me immense satisfaction and calmness.  Make my mind filled with a particular ray of hope for solving or fighting out any problematic situations. We all know how complicated life is for an individual, including work satisfaction, work pressure, personal commitments, etc. We don't always analyze every situation at one go; we take it slowly and carefully. Similarly, when you cycle at full speed, you often get to have inevitable distractions. For example, someone is asking for a pass; someone is constantly behind to surpass you; someone wants to ride faster than you; someone will come from another way without following any rules. We take it one by one depending upon our strengths and weakn...
3 comments
Read more

Manage Meals Every Day?

My friends ask me how can you just manage to prepare meals every day and carry it? My answer to them has always been it's a matter of practice and you should not have expectations from anybody. I believe I have learned from my mother and my sisters. My school days were actually difficult for me in terms of Travelling. I failed to understand who coined the term FIFO. That did not work for me at all. Like every other day, my school bus used to come at 6:45 am to pick me from Ashok Raj Path, opposite to B.N. College, Patna. I was lucky enough that Mummy used to get up early to prepare meals for me. Gradually, my sister Shalini took that responsibility for a limited period. I used to enjoy my window seat on the bus, seeing people traveling with different means of Transportation. My energy level used to be so high and this was the reason to organize assemblies like Reading News, Singing National Anthem, etc. I loved them all! My school bus used to do Daily two trips and I w...
1 comment
Read more

Unsaid Things

No doubts there are many people (especially my family members) in my life who has always motivated me and want me to work on myself. This time, I would like to draw some instances when I was completed shattered personally and professionally. There are various instances but would share 5 to 6 from my end. Here you go!  1st Time - April 2017 during the second week  My sister -Shaily Choudhary was travelling to India with her family. I was so excited to see Shivi (my niece) for the first time because I used to enjoy playing with her on Skype calls. We had plans to roam together. But could not happen as we thought of. In between, my guardian was trying to fix an alliance for me as usual. The guy's family was supposed to see me and wanted to take the matter forward.The guy was working in Dubai for the last 8 to 10 years. But when we did some homework regarding his profile, we found that he was 36 or 38 years old. His parents hid his age from us. It did not bother much ...
Post a Comment
Read more