SQL Interview Question Attempt

What are some technical interview questions that applicants will see in an interview? Below is an example of a SQL technical interview question.

Ref. From https://www.interviewqs.com/?ref=ddi_email

Due to our small data set, we can solve this problem by hand before we move onto getting the SQL query. To get the revenue growth for online channel, we need the current month’s revenue which is 225 for September and prior month’s revenue which is 200 for August. Our revenue growth would be 12.5%.

But how would you go about writing up the SQL code for this type of problem? Here is my thought process leading to getting the solution. For those who want to follow along with the practice, we are using MySQL v8.0 through an online website. But to create the table you can use the code below.

CREATE TABLE orders (order_id INTEGER PRIMARY KEY,
                     channel TEXT,
                     date TEXT,
                     month INTEGER,
                     revenue INTEGER);
INSERT INTO orders VALUES (1, "Online", "2018-09-01", 9, 100);
INSERT INTO orders VALUES (2, "Online", "2018-09-03", 9, 125);
INSERT INTO orders VALUES (3, "In_store", "2018-10-11", 10, 200);
INSERT INTO orders VALUES (4, "In_store", "2018-08-21", 8, 80);
INSERT INTO orders VALUES (5, "Online", "2018-08-13", 8, 200);

First let’s ask ourselves what we ultimately want in our final table to display. We want our final graph to display channel, month, total revenue, prior month revenue and growth. Therefore the code below we show what we are selecting.

SELECT channel, 
       month, 
       total_revenue, 
       Prior_Month, 
       ((total_revenue - Prior_Month)/Prior_month) AS growth

Note that we need to define what total_revenue is and what Prior_Month is. This will be done in a subquery to make it look nicer. But before we talk about the subquery we need to figure out two things.

1. What do we need to group together to calculate the total revenue?
2. How do we get the prior month’s total revenue?

Since we want the monthly total revenue for the online channel. Therefore we would need to calculate the total revenue by grouping by month follow by channel and isolating just the online channel. Now to get the prior month’s revenue we would need to use a Lag function on the total revenue to shift everything by 1 month. Putting all this together, our subquery should look something like this.

SELECT channel, 
       month, 
       SUM(revenue) AS total_revenue, 
       Lag(SUM(revenue), 1, 0) OVER(ORDER BY month) AS Prior_Month
FROM orders
GROUP BY month, channel
HAVING channel = 'Online'

After getting our subquery, we can put all this together to get our the table that we want.

SELECT channel, 
       month, 
       total_revenue, 
       Prior_Month, 
       ((total_revenue-Prior_Month)/Prior_Month) AS growth
FROM(
     SELECT channel, 
            month, 
            SUM(revenue) AS total_revenue, 
            Lag(SUM(revenue), 1, 0) OVER(ORDER BY month) AS Prior_Month
     FROM orders
     GROUP BY month, channel
     HAVING channel = 'Online') AS revenue_table;

Below displays the table that we want to show the revenue growth of the online channel.

For the growth column, the first row displays null value because we don’t have any prior month results for the month of August. This is the process I would take to solve any type of SQL question.