Average Deal Size (Part 2)
Assume Salesforce customers pay on a per user basis (also referred to as per seat model). Given a table of contracts data, write a query to calculate the average annual revenue per Salesforce customer in the three market segments. Output the results as per the example output below.
Explanation: Datalemur is classified as the only SMB in the example data. They have only one contract including 50 seats with the price of 1250.
First, let's break down the problem into simple steps.
Let's begin. If you look at the tables and closely, they do not specify market segment anywhere. Luckily, the question mentioned that the segments are defined based on the .
With the help of a conditional statement, the market segments can be assigned. statements allow you to execute a block of code based on a condition. Click here to learn more about them. We will using a subquery to use the statements. Let's call it segment-subquery.
The next step is to combine and data available in the segment-subquery using . Please read here to get the fair idea about them. This way, all the required columns to calculate the average annual revenue per Salesforce customer in the three market segments can be brought together and the following formula can be used.
Average deal size = (Number of seats x Yearly seat cost) / (Number of contracts)
It can be converted to SQL codes using the combination of aggregated functions  and . Since the question requires us to do the calculation on market segment level,  clause will come in handy here.
modifier is used on an aggregate function to limit the values in an aggregation. You can visit this link for an example of is used.
Now, we will combine everything we have learnt to construct a SQL query.