SQL: Revenue by City

Easy
SQL
spark.sql
Aggregation
Databricks
Amazon

SQL: Revenue by City

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.

Browser Engine: Evaluates PySpark DataFrame & SQL logic locally in Pyodide WebAssembly.

Input data

2 DataFrames

These DataFrames are passed to solve(spark, dfs). Access one with dfs['name'].

df.orders4 rows
order_id (int)user_id (int)amount (double)
110125
210240
310110
410355
df.users3 rows
user_id (int)name (string)city (string)
101AshaBengaluru
102BilalMumbai
103ChenDelhi

Return these columns

city(string)
total_amount(double)

Column names and order must match. Sort order is specified in the question.

Browser engine
Loading Python editor…
Click Run to try the sample cases, or Submit to check the hidden cases. First run caches the browser Python engine (~8–15s once); all code stays on your device.