Challenging Advanced SQL Interview Questions 2024 With Easy Explanation

Introduction: Advanced SQL interview questions from a Recent Interview in 2024

Hello Guys This article is about Real interview Questions based on Advance SQL Questions. I have attempted interviews where these Questions were asked in the SQL Technical Round.

Instructions: 

a. You must attempt the questions mentioned below Using the Order table and User table.

b. Only the Select statement has to be used for queries 

c. Query can be written anywhere – word, notepad, etc.

d. Our intent: Logic and Syntactical check (so you won’t require raw data tables to do these questions)

1: From the Orders table find

– Daily new customers (who placed their first order)
– Daily old customers (who placed their repeat orders)

– Daily customers who have placed their 3rd-lifetime order 

Sample Output:

2: From the Orders table and user table to find Orders placed with the referral codes of Vidhisha, Ranu, Jhanvi

Sample Output:

3: From the Orders Table find the daily order contribution percentage of sellers

Sample Output:

Solution:

# assumptions
— 1. 3rd lifetime order = the 3rd order of entire order history where 3rd order doesn’t have to be last order
— 2. the customer who is placing his/her 3rd order on a particular day doesn’t have to be necessarily an old customer,it could be a
— an old as well as a new customer
— 3. a customer will be considered a new customer on a particular date if his/her first purchase date is the date being considered for,
— even if she/he has made multiple purchases on the same date. These assumptions are made because this is required for SQL interview questions for experienced Data Analyst. You can suggest or ask to interviewer at the time of Sql Interview Round.

Solution:1)

<!-- wp:enlighter/codeblock {"language":"raw"} -->
<pre class="EnlighterJSRAW" data-enlighter-language="raw" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">         
WITH first_purchase_info AS (
               SELECT customer_id, min(date(order_time)) as first_purchase_date
                      FROM orders
                      GROUP BY customer_id),
         third_purchase_info as (
               SELECT customer_id, order_time as third_order_time, 
                              order_date as third_order_date
               FROM (SELECT customer_id, order_time, date(order_time) as order_date,
&nbsp; &nbsp; &nbsp; &nbsp;        ROW_NUMBER() over(partition by customer_id order by order_time) as purchase_idx
&nbsp; &nbsp; &nbsp; &nbsp;               FROM orders)
         WHERE purchase_idx = 3)
         final_orders_data as (
               SELECT date(t1.order_time) as date,
         CASE WHEN date(t1.order_time) = t2.first_purchase_date THEN 1 ELSE 0 end as new_customer,
         CASE WHEN date(t1.order_time) &gt; t2.first_purchase_date THEN 1 ELSE 0 end as old_customer,
         CASE WHEN date(t1.order_time) = t3.third_order_date THEN 1 ELSE 0 end as 3rd_order_placed
                       FROM  orders t1
                  JOIN first_purchase_info t2
                    ON t1.customer_id = t2.customer_id
                  JOIN  third_purchase_info t3
                    ON t1.customer_id = t3.customer_id
                              )
         SELECT date, sum(new_customer) as new_customers, 
         SUM(old_customer) as old_customers, SUM(3rd_order_placed) as 3rd_order_placed
         FROM final_orders_data
         GROUP BY date
         ORDER BY date</pre>
<!-- /wp:enlighter/codeblock -->

Solution:2)

         SELECT t2.first_name, t1.num_orders as orders_via_referral
                FROM(
         SELECT referral_code, count(order_id) as num_orders
                FROM orders
         WHERE referral_code in ('Vidhisha', 'Ranu', 'Jhanvi')
         GROUP BY referral_code) t1
         JOIN users t2
         ON t1.referral_code = t2.referral_code

Solution: 3)

         WITH seller_sales_info AS (
         SELECT DATE(order_time) as order_date, seller_name, 
              COUNT(order_id) as num_orders
              FROM orders
         GROUP BY  date(order_time), seller_name)
         SELECT order_date, seller_name, num_orders/(SUM(num_orders) OVER(partition by order_date)) 
              AS order_contribution,
              SUM(num_orders) OVER(partition by order_date) AS total_orders
              FROM seller_sales_info
              ORDER BY order_date, seller_name;

Cars24 Data Analyst SQL Interview Questions for Experienced

Question 1: Below is a sample table. Please write a SQL query based on the below table to return a count of people whose grandparents are alive. These are very good SQL Interview Questions for those Experienced for 3+ years mostly asked in the Cars24 Data Analyst Technical round.

        SELECT COUNT(*) AS GrandparentAliveCount
               FROM TableName AS P
               JOIN TableName AS G ON P.Name_of_Parent = G.Name_of_Person
               JOIN TableName AS GG ON G.Name_of_Parent = GG.Name_of_Person
               WHERE P.Status_of_Person = 'Alive' AND GG.Status_of_Person = 'Alive';

Conclusion

In conclusion, navigating Advanced SQL interview questions for experienced professionals underscores the depth of knowledge and expertise required in the field. As candidates delve into complex queries, intricate database designs, and advanced concepts, it becomes evident that experience not only sharpens technical skills but also cultivates problem-solving abilities.

A successful engagement with these interviews showcases proficiency and a seasoned understanding of database management, making candidates poised for impactful contributions in the dynamic realm of data and technology.

Smart practice is the key to success for Advanced SQL interview questions. You need to practice under time pressure because it is the key element of SQL interviews.

Also, Read SQL Complex Interview Questions. Bookmark this Page for more SQL Interview Questions for Experienced data analysts.

Editorial Team