Write a Spark query to process the following data.

Question

Assisting AI · Accepted Answer

Assuming you have a DataFrame `df` with columns `category`, `value`, and `timestamp`, and you want to compute the average value per category for the last 30 days, you can write the following Spark SQL query:

```python
from pyspark.sql import functions as F
from pyspark.sql.window import Window

# Register the DataFrame as a temporary view
df.createOrReplaceTempView("sales")

# Spark SQL query
query = (
    "SELECT category, AVG(value) AS avg_value, COUNT(*) AS cnt "
    "FROM sales "
    "WHERE timestamp >= date_sub(current_date(), 30) "
    "GROUP BY category"
)
result = spark.sql(query)
result.show()
```

If you prefer the DataFrame API, the equivalent code is:

```python
from pyspark.sql import functions as F

result = (
    df.filter(F.col("timestamp") >= F.date_sub(F.current_date(), 30))
      .groupBy("category")
      .agg(F.avg("value").alias("avg_value"), F.count("*" ).alias("cnt"))
)
result.show()
```

Both approaches produce a DataFrame with the average value and count per category for the last month. The complexity is O(n) for the filter and O(n) for the groupBy aggregation, which is efficient for large datasets.

Write a Spark query to process the following data.

💡 Model Answer

🎤 Get questions like this answered in real-time