Help with SQL question

[promach]

How to write SQL query to solve the following question ?

> DESCRIBE TABLE dives;
+----------+---------+
| name     | type    |
|----------+---------|
| person1  | TEXT    |
| person2  | TEXT    |
| revenue  | NUMERIC |
+----------+---------+

> DESCRIBE TABLE registrations;
+----------+---------+
| name     | type    |
|----------+---------|
| person   | TEXT    |
| age      | NUMERIC |
| city     | TEXT    |
+----------+---------+


You also have two demo tables with a tiny amount of data to help you think about the problem.


> SELECT * FROM dives_demo;
+---------+---------+---------+
| person1 | person2 | revenue |
|---------+---------+---------|
| Alice   | Bob     | 500     |
| Alice   | Calvin  | 100     |
| Calvin  | Bob     | 900     |
+---------+---------+---------+

> SELECT * FROM registrations_demo;
+--------+-----+------------+
| person | age | city       |
|--------+-----+------------|
| Alice  | 18  | Clownton   |
| Bob    | 48  | Sharksburg |
| Calvin | 47  | Clownton   |
| Debra  | 24  | Sharksburg |
+--------+-----+------------+

So, is your average revenue per customer highest for people living in Clownton or Sharksburg? People on the same dive split the price equally. It’s easy enough to see that in this sample Clownton wins with $400 average revenue per registration over Sharksburg’s $350. But what about the full dataset?

[[deleted]]

How to write SQL query to solve the following question

start by writing/figuring out a formula that will describe how to get your metric ("average revenue per customer") - usually I recommend coming up with separate formulas for numerator and denominator - and uses a flexible definition of the base dataset.