logo

Back to questions

Average Deal Size (Part 2)

Medium

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)

Table:

Column NameType
customer_idinteger
num_seatsinteger
yearly_seat_costinteger

Example Input:

customer_idnum_seatsyearly_seat_cost
26905025
452020050
452015050
452015050
783287850

Table:

Column NameType
customer_idinteger
namevarchar
employee_countinteger (0-100,000)

Example Input:

customer_idnameemployee_count
4520DBT Labs500
2690DataLemur99
7832GitHub878

Example Output:

smb_avgmid_avgenterprise_avg
12504390025000

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

PostgreSQL 14

Refer friends to get bonus content & cool prizes.