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.

Assumptions:

- Yearly seat cost refers to the cost per seat.
- Each customer represents one contract.
- The market segments are:-
- SMB (<100 employees)
- Mid-Market (100-999 employees)
- Enterprise (>=1000 employees)

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

customer_id | integer |

num_seats | integer |

yearly_seat_cost | integer |

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

2690 | 50 | 25 |

4520 | 200 | 50 |

4520 | 150 | 50 |

4520 | 150 | 50 |

7832 | 878 | 50 |

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

customer_id | integer |

name | varchar |

employee_count | integer (0-100,000) |

customer_id | name | employee_count |
---|---|---|

4520 | DBT Labs | 500 |

2690 | DataLemur | 99 |

7832 | GitHub | 878 |

smb_avg | mid_avg | enterprise_avg |
---|---|---|

1250 | 43900 | 25000 |

Explanation: Datalemur is classified as the only SMB in the example data. They have only one contract including 50 seats with the price of $25 each =$1250.

First, let's break down the problem into simple steps.

- Categorise customers into market segments.
- Calculate the average annual revenue by market segment.
- Convert the result into desired output format, i.e. smb_avg, mid_avg, enterprise_avg.

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 [1] and [2]. Since the question requires us to do the calculation on market segment level, [3] clause will come in handy here.

Final step is to convert the results obtained so far into the requested format. We will enclose the query from the previous steps into a CTE [4] and apply the aggregate [5] on this CTE block.

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.

PostgreSQL 14