Back to questions

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.

Column Name | Type |
---|---|

customer_id | integer |

num_seats | integer |

yearly_seat_cost | integer |

customer_id | num_seats | yearly_seat_cost |
---|---|---|

2690 | 20 | 1000 |

2561 | 50 | 500 |

4520 | 100 | 500 |

9875 | 40 | 1000 |

5260 | 150 | 100 |

average_deal_size |
---|

30000.00 |

The dataset you are querying against may have different input & output - **this is just an example**!

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