You can write Spark SQL directly with spark.sql("""...""").
orders has order_id, user_id, amount. users has user_id, name, city.
Register both as temp views, then use spark.sql() to return city, total_amount
sorted by city:
def solve(spark, dfs):
orders = dfs['orders']
users = dfs['users']
orders.createOrReplaceTempView('orders')
users.createOrReplaceTempView('users')
return spark.sql("""
SELECT u.city, SUM(o.amount) AS total_amount
FROM orders o
JOIN users u ON o.user_id = u.user_id
GROUP BY u.city
ORDER BY u.city
""")
Everything runs in the browser - no server execution.
These DataFrames are passed to solve(spark, dfs). Access one with dfs['name'].
orders4 rows| order_id (int) | user_id (int) | amount (double) |
|---|---|---|
| 1 | 101 | 25 |
| 2 | 102 | 40 |
| 3 | 101 | 10 |
| 4 | 103 | 55 |
users3 rows| user_id (int) | name (string) | city (string) |
|---|---|---|
| 101 | Asha | Bengaluru |
| 102 | Bilal | Mumbai |
| 103 | Chen | Delhi |
Column names and order must match. Sort order is specified in the question.