Back to questions
Average Deal Size (Part 1) [Salesforce SQL Interview Question]
Assume that 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. Round your answer to 2 decimal places.
Assume each customer only has 1 contract and the yearly seat cost refers to cost per seat.
Table:
| Column Name | Type |
|---|---|
| customer_id | integer |
| num_seats | integer |
| yearly_seat_cost | integer |
Example Input:
| customer_id | num_seats | yearly_seat_cost |
|---|---|---|
| 2690 | 20 | 1000 |
| 2561 | 50 | 500 |
| 4520 | 100 | 500 |
| 9875 | 40 | 1000 |
| 5260 | 150 | 100 |
Example Output:
| average_deal_size |
|---|
| 30000.00 |
The dataset you are querying against may have different input & output - this is just an example!
Solution
The following formula can be used to calculate the average annual revenue per Salesforce customer.
Average deal size = (Number of seats x Yearly seat cost) / (Number of contracts)
table doesn't have any column specific to the contract. As mentioned in the question, the assumption is one customer will have only one contract. Hence, it means that the number of contracts = number of customers present in the table.
How to convert the formula into SQL codes
Aggregate function allows you to get the number of rows and returns the sum of the values.
Average deal size in SQL will look like this:
Read about aggregate functions [1] and [2] to know more in detail.
[3] function rounds a numeric value to its nearest integer or a number with the number of decimal places.
Now, we combine everything we have learnt to construct a SQL query.
PostgreSQL 14