Above Average Product Prices
Start Timer
0:00:00
Given a table of transactions and products, write a query to retrieve three columns product_id, product_price, and avg_transaction_total where:
avg_transaction_totalis the average of(price * quantity)over all transactions for each product.We only want products whose
product_priceis strictly greater than their ownavg_transaction_total.
Notes: - Round the values to two decimal places.
Schema
Input:
transactions table
| Column | Type |
|---|---|
id |
INTEGER |
user_id |
INTEGER |
created_at |
DATETIME |
product_id |
INTEGER |
quantity |
INTEGER |
products table
| Column | Type |
|---|---|
id |
INTEGER |
name |
VARCHAR |
price |
FLOAT |
Example
Input:
products table
| id | name | price |
|---|---|---|
| 1 | few local receive | 37.32 |
| 2 | land debate here | 24.98 |
| 3 | dinner suffer truth | 175.47 |
| 4 | sometimes tonight billion | 172.0 |
| 5 | person require analysis | 80.0 |
| 6 | stop of bring | 37.0 |
| 7 | loss weight task | 182.0 |
| 8 | air sister final | 11.0 |
| 9 | sister wish station | 23.0 |
| 10 | energy itself cause | 21.95 |
transactions table
| id | user_id | created_at | product_id | quantity |
|---|---|---|---|---|
| 100 | 50 | 2019-01-19 00:00:00 | 5 | 2 |
| 101 | 50 | 2019-01-19 00:00:00 | 3 | 3 |
| 102 | 51 | 2019-01-21 00:00:00 | 8 | 1 |
| 103 | 52 | 2019-01-22 00:00:00 | 7 | 1 |
| 104 | 53 | 2019-01-23 00:00:00 | 2 | 1 |
| 105 | 53 | 2019-01-23 00:00:00 | 4 | 3 |
| 106 | 54 | 2019-01-25 00:00:00 | 1 | 5 |
| 107 | 55 | 2019-01-26 00:00:00 | 6 | 2 |
| 108 | 55 | 2019-01-26 00:00:00 | 8 | 3 |
| 109 | 56 | 2019-01-28 00:00:00 | 9 | 1 |
| 110 | 57 | 2019-01-29 00:00:00 | 10 | 2 |
Output:
| product_id | product_price | avg_transaction_total |
|---|---|---|
| 3 | 175.47 | 161.9 |
| 4 | 172.0 | 161.9 |
| 7 | 182.0 | 161.9 |
.
.
.
.
.
.
.
.
.
Comments