SQL Query: Immediate Food Delivery solved using CTE

QUESTION: Table: Delivery

+-----------------------------+---------+
| Column Name                 | Type    |
+-----------------------------+---------+
| delivery_id                 | int     |
| customer_id                 | int     |
| order_date                  | date    |
| customer_pref_delivery_date | date    |
+-----------------------------+---------+
delivery_id is the primary key of this table.
The table holds information about food delivery to customers that make orders at some date and specify a preferred delivery date (on the same order date or after it).


If the preferred delivery date of the customer is the same as the order date then the order is called immediate otherwise it's called scheduled.

The first order of a customer is the order with the earliest order date that customer made. It is guaranteed that a customer has exactly one first order.

Write an SQL query to find the percentage of immediate orders in the first orders of all customers, rounded to 2 decimal places.

The query result format is in the following example:

Delivery table:
+-------------+-------------+------------+-----------------------------+
| delivery_id | customer_id | order_date | customer_pref_delivery_date |
+-------------+-------------+------------+-----------------------------+
| 1           | 1           | 2019-08-01 | 2019-08-02                  |
| 2           | 2           | 2019-08-02 | 2019-08-02                  |
| 3           | 1           | 2019-08-11 | 2019-08-12                  |
| 4           | 3           | 2019-08-24 | 2019-08-24                  |
| 5           | 3           | 2019-08-21 | 2019-08-22                  |
| 6           | 2           | 2019-08-11 | 2019-08-13                  |
| 7           | 4           | 2019-08-09 | 2019-08-09                  |
+-------------+-------------+------------+-----------------------------+

Result table:
+----------------------+
| immediate_percentage |
+----------------------+
| 50.00                |
+----------------------+
The customer id 1 has a first order with delivery id 1 and it is scheduled.
The customer id 2 has a first order with delivery id 2 and it is immediate.
The customer id 3 has a first order with delivery id 5 and it is scheduled.
The customer id 4 has a first order with delivery id 7 and it is immediate.
Hence, half the customers have immediate first orders.

SOLUTION:

Step1) Try to find first order of each customer using dense rank function
Step2) Get the immediate order from the first order set 
Step3) Get the percentage of immediate order to the total first order 

;with FirstOrderCTE as
(select 
 delivery_id, customer_id, order_date, 
 customer_pref_delivery_date, 
 dense_rank() over (partition by customer_id order by order_date asc) as rnk 
from Delivery
)
,immediate_order as
(select count(*) as immediate_order_count 
 from FirstOrderCTE 
 where rnk = 1 and order_date = customer_pref_delivery_date
)
 ,percent_of_immediate_order as
 (select 
  cast(((select immediate_order_count from immediate_order limit 1)/
  cast(count(*) as numeric(8,2))) * 100 as numeric(8,2)) as immediate_percentage,
  
  (select immediate_order_count from immediate_order limit 1),
  cast(count(*) as numeric(8,2))
  from FirstOrderCTE where rnk = 1
 )
 --select immediate_order_count from immediate_order limit 1
 select immediate_percentage from percent_of_immediate_order

Post Reference: Vikram Aristocratic Elfin Share

SQL Query: Biggest Window Between Visits

QUESTION: Table: UserVisits

+-------------+------+
| Column Name | Type |
+-------------+------+
| user_id     | int  |
| visit_date  | date |
+-------------+------+
This table does not have a primary key.
This table contains logs of the dates that users vistied a certain retailer.


Assume today's date is '2021-1-1'.

Write an SQL query that will, for each user_id, find out the largest window of days between each visit and the one right after it (or today if you are considering the last visit).

Return the result table ordered by user_id.

The query result format is in the following example:



UserVisits table:
+---------+------------+
| user_id | visit_date |
+---------+------------+
| 1       | 2020-11-28 |
| 1       | 2020-10-20 |
| 1       | 2020-12-3  |
| 2       | 2020-10-5  |
| 2       | 2020-12-9  |
| 3       | 2020-11-11 |
+---------+------------+
Result table:
+---------+---------------+
| user_id | biggest_window|
+---------+---------------+
| 1       | 39            |
| 2       | 65            |
| 3       | 51            |
+---------+---------------+
For the first user, the windows in question are between dates:
    - 2020-10-20 and 2020-11-28 with a total of 39 days. 
    - 2020-11-28 and 2020-12-3 with a total of 5 days. 
    - 2020-12-3 and 2021-1-1 with a total of 29 days.
Making the biggest window the one with 39 days.
For the second user, the windows in question are between dates:
    - 2020-10-5 and 2020-12-9 with a total of 65 days.
    - 2020-12-9 and 2021-1-1 with a total of 23 days.
Making the biggest window the one with 65 days.
For the third user, the only window in question is between dates 2020-11-11 and 2021-1-1 with a total of 51 days.

Solution: 

Step1) First Generate the row number over partition by user_id
Step2) Then implement lag lead to get the previous date visit
Step3) fin difference of latest date with prev date
step 4) get the max days diff on the basis of user_id

with UserVisitCTE as 
(select row_number() over(partition by user_id order by user_id, visit_date ) as rno, * from UserVisits 
order by user_id, visit_date
 ),
 UserVisitLeadCTE as
 (
     select f.user_id, COALESCE(s.visit_date,'2021-01-01'), f.visit_date
     ,(COALESCE(s.visit_date,'2021-01-01') - f.visit_date) as days_window
     from UserVisitCTE f
     left join UserVisitCTE s
     on f.rno + 1 = s.rno and f.user_id = s.user_id
 )
 --select * from UserVisitCTE
 select user_id, max(days_window) as biggest_window 
 from UserVisitLeadCTE 
 group by user_id
 order by user_id

Post Reference: Vikram Aristocratic Elfin Share

SQL Query: Exchange Seats Problem

Mary is a teacher in a middle school and she has a table seat storing students' names and their corresponding seat ids.

The column id is continuous increment.

Mary wants to change seats for the adjacent students.

Can you write a SQL query to output the result for Mary?



+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Abbot   |
|    2    | Doris   |
|    3    | Emerson |
|    4    | Green   |
|    5    | Jeames  |
+---------+---------+
For the sample input, the output is:

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Doris   |
|    2    | Abbot   |
|    3    | Green   |
|    4    | Emerson |
|    5    | Jeames  |
+---------+---------+
Note:

If the number of students is odd, there is no need to change the last one's seat.

Solution:

• Step1) We get the even id set of student
• Step2) we get the odd id set of student
• Step3) get the even id with odd student name
• step4) get the odd id with even student name
• step5) union and sort step3 and step4

;with even as 
(select * from seat s1 where s1.id%2 = 0)
,odd as 
(select * from seat s2 where s2.id%2 = 1)
,comb as 
(
select e.id, COALESCE(o.student,e.student) as student 
 from even e left join odd o
	on e.id = o.id +1
union all
select o.id, COALESCE(e.student,o.student) as student 
    from odd o left join even e
		on o.id + 1 = e.id
)

select * from comb order by id asc

Post Reference: Vikram Aristocratic Elfin Share

SQL Query: Winning Candidate

Table: Candidate

 +-----+---------+
 | id  | Name    |
 +-----+---------+
 | 1   | A       |
 | 2   | B       |
 | 3   | C       |
 | 4   | D       |
 | 5   | E       |
 +-----+---------+  
Table: Vote
 +-----+--------------+
 | id  | CandidateId  |
 +-----+--------------+
 | 1   |     2        |
 | 2   |     4        |
 | 3   |     3        |
 | 4   |     2        |
 | 5   |     5        |
 +-----+--------------+
id is the auto-increment primary key,
CandidateId is the id appeared in Candidate table.
Write a sql to find the name of the winning candidate, the above example will return the winner B.

 +------+
 |  Name |
 +------+
 | B    |
 +------+
Notes:

You may assume there is no tie, in other words there will be only one winning candidate.

Solution:

PL/SQL:

select c.Name from Candidate c 

inner join vote v

on c.id = v.CandidateId

group by c.Name

order by count(*) desc

limit 1

TSQL:

select top 1 c.Name  from Candidate c 

inner join vote v

on c.id = v.CandidateId

group by c.Name

order by count(*) desc

Post Reference: Vikram Aristocratic Elfin Share

SQL Query: Rising Temperature Days

Table: Weather

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| recordDate    | date    |
| temperature   | int     |
+---------------+---------+
id is the primary key for this table.
This table contains information about the temperature in a certain day.


Write an SQL query to find all dates' id with higher temperature compared to its previous dates (yesterday).

Return the result table in any order.

The query result format is in the following example:

Weather
+----+------------+-------------+
| id | recordDate | Temperature |
+----+------------+-------------+
| 1  | 2015-01-01 | 10          |
| 2  | 2015-01-02 | 25          |
| 3  | 2015-01-03 | 20          |
| 4  | 2015-01-04 | 30          |
+----+------------+-------------+

Result table:
+----+
| id |
+----+
| 2  |
| 4  |

Solution:

select distinct w2.id from Weather w1 
inner join Weather w2
on w1.id + 1 = w2.id 
where w1.Temperature < w2.Temperature

Post Reference: Vikram Aristocratic Elfin Share