# SQL Nested Subquery Example Interview Question

**Overview of Nested Queries**

A SQL nested query, also known as a subquery or subselect, is a SELECT query method embedded within another query. It’s an efficient way to get the desired output that may require multiple query steps or to handle queries that are interdependent.

**When to use Nested Subqueries**

You can use nested subqueries in a couple different ways:

- You can query over subqueries (e.g. selecting from a subquery)
- You can replace 1D arrays (e.g. a typical list of items) and single field joins with a single subquery in the WHERE or HAVING clause

In order to use a subquery, there are a couple of syntactical rules that need to be followed:

- The subquery needs to be enclosed within parentheses
- Depending on the SQL engine you are using, you might need to alias a given subquery
- If using in a WHERE or HAVING clause, the SELECT statement of an subquery can only return the single field being evaluated

**Example SQL interview question using a nested subquery**

Suppose you are given the following table showing company sales:

Table: **sales_info**

date | sale_id | sale_usd |
---|---|---|

2020-01-05 | 1111 | 93695 |

2020-01-07 | 1112 | 879617 |

2020-01-07 | 1113 | 752878 |

... | ... | ... |

**Calculate the cumulative percent of total sales on a given day.** The output of the table should look like the example table below.

date | pct_total_sales |
---|---|

2020-01-05 | X% |

2020-01-07 | Y% |

You can work through this example using the interactive SQL fiddle here.

Before we start writing SQL, we'll break the question into steps:

- Calculate the daily sales total
- Calculate the cumulative sum of the daily sales total and total sales for all days
- Divide the daily total sales by the cumulative sum

**1. Calculate the daily sales total**

First, we'll write the base query, which will become a subquery in the next step. The below query calculates the daily sales total and you can interact with the query with this SQL fiddle.

```
SELECT
#we'll need to sum sale_usd by the date
date,
sum(sale_usd) as total_usd
FROM sales_info
#because we're aggregating sale_usd by date, we need
#need to group by date
GROUP BY date
```

**2. Calculate the cumulative sum of the daily sales total and total sales for all days**

The query below calculates the cumulative sum of the daily sales total and total sales for all days. You can interact with the query below with this SQL fiddle.

You'll notice in this step that we build a query around the previous query from the step above. In order to successfully build the subquery, we needed to enclose the subquery in parentheses and ensure that we alias the table (since we're using MySQL, note that this isn't a requirement for some SQL variants). We also need to ensure that we include all fields needed for the outer query (e.g. date and total sales).

```
SELECT
#in this query, we do not group by since we're using a windowing function
date,
SUM(total_usd) OVER (
ORDER BY date ASC rows
BETWEEN unbounded preceding and current row)
as cum_total, # this is a windowing function to
# calculate the cumulative sum
SUM(total_usd) OVER () as total # this is a windowing function to
# calculate the total
FROM(
SELECT
#we'll need to sum sale_usd by the date
date,
sum(sale_usd) as total_usd
FROM sales_info
#because we're aggregating sale_usd by date, we need
#need to group by date
GROUP BY date
) as q1 #we create an alias for this table as required by MySQL
```

**3. Divide the cumulative total sales by the cumulative sum**

The last step is to divide the `cum_total`

by the total. We can accomplish this in the same step as above (just dividing the two windowing functions), or we can build a subquery over the previous step. The query below uses another subquery resulting in the end query having two nested subqueries. You can interact with the query below with this SQL fiddle.

```
SELECT
date,
100 * cum_total / total as
FROM(
#in this query, we do not group by
#since we're using a windowing function
SELECT
date,
SUM(total_usd) OVER (ORDER BY date ASC
rows BETWEEN unbounded preceding and current row)
as cum_total, # this is a windowing function to
# calculate the cumulative sum
SUM(total_usd) OVER () as total # this is a windowing function
# to calculate the total
FROM(
SELECT
#we'll need to sum sale_usd by the date
date,
sum(sale_usd) as total_usd
FROM sales_info
#because we're aggregating sale_usd by date, we need
#need to group by date
GROUP BY date
) as q1 #we create an alias for this table as required by MySQL
) as q2
```