Back to questions
Average Deal Size (Part 2) Salesforce SQL Interview Question
Average Deal Size (Part 2)
Salesforce SQL Interview Question
Assuming Salesforce operates on a per user (per seat) pricing model, we have a table containing contracts data.
Write a query to calculate the average annual revenue per Salesforce customer in three market segments: SMB, Mid-Market, and Enterprise. Each customer is represented by a single contract. Format the output to match the structure shown in the Example Output section below.
Assumptions:
- Yearly seat cost refers to the cost per seat.
- Each customer is represented by one contract.
- The market segments are categorized as:-
- SMB (less than 100 employees)
- Mid-Market (100 to 999 employees)
- Enterprise (1000 employees or more)
- The terms "average deal size" and "average revenue" refer to the same concept which is the average annual revenue generated per customer in each market segment.
Table:
| Column Name | Type |
|---|---|
| customer_id | integer |
| num_seats | integer |
| yearly_seat_cost | integer |
Example Input:
| customer_id | num_seats | yearly_seat_cost |
|---|---|---|
| 2690 | 50 | 25 |
| 4520 | 200 | 50 |
| 4520 | 150 | 50 |
| 4520 | 150 | 50 |
| 7832 | 878 | 50 |
Table:
| Column Name | Type |
|---|---|
| customer_id | integer |
| name | varchar |
| employee_count | integer (0-100,000) |
Example Input:
| customer_id | name | employee_count |
|---|---|---|
| 4520 | DBT Labs | 500 |
| 2690 | DataLemur | 99 |
| 7832 | GitHub | 878 |
Example Output:
| smb_avg_revenue | mid_avg_revenue | enterprise_avg_revenue |
|---|---|---|
| 1250 | 25000 | 43900 |
Explanation:
SMB Average : DataLemur (customer ID 2690) is classified as the only SMB customer in the example data. They have a single contract with 50 seats and a yearly seat cost of $25. Therefore, the average annual revenue is: (50 * 25) / 1 = $1,250.
Mid-Market Average : DBT Labs (customer ID 4520) is the only Mid-Market customer in the example data. They have 3 contracts with a total of 500 seats and a yearly seat cost of $50. Thus, the average annual revenue is: (500 * 50) / 1 = $25,000
Enterprise Average : GitHub (customer ID 7832) is the only Enterprise customer in the example data. They have one contract with 878 seats and a yearly seat cost of $50. Therefore, the average annual revenue per Enterprise customer is: (878 * 50) / 1 = $43,900.
The dataset you are querying against may have different input & output - this is just an example!
Solution
Step 1: Categorize customers into market segments
To begin, we need to categorize customers into market segments based on their employee count. The tables and do not directly provide the market segment information, but we can determine it based on the column.
We will use a conditional statement to assign the market segments. You can learn more about statements here.
Step 2: Calculate the average annual revenue by market segment
Next, we need to calculate the average annual revenue per customer in each market segment. We can achieve this by combining the contracts table with the data from the segment-subquery using INNER JOIN. Understanding JOINS is important, and you can read more about them here.
By bringing together the necessary columns, we can use the following formula to calculate the average revenue:
Average revenue = (Number of seats x Yearly seat cost) / (Number of contracts)
Step 3: Convert the result into desired output format
The final step involves converting the obtained results into the requested format. We will enclose the query from the previous steps and apply the aggregate modifier.
The modifier is a PostgreSQL-specific feature that allows you to apply a condition within an aggregate function to selectively include or exclude values from the calculation. You can refer to this link for an example of how is used.
Here's another approach to achieve conditional filtering in aggregate calculations is by using the function in combination with a statement. This method is not specific to PostgreSQL and can be used with any SQL flavour: