Calculate a 7-day moving average of orders for each city in the Swiggy database.

To calculate a 7-day moving average of orders for each city, use a SQL window function. This involves partitioning the data by city_id, ordering it by order_date, and defining a window that includes the current row and the six preceding rows.

Mechanics and Why
A moving average helps smooth out daily fluctuations, revealing underlying trends. The PARTITION BY city_id clause is crucial as it ensures the moving average is calculated independently for each city, preventing data from one city from influencing another. The ORDER BY order_date clause establishes the chronological sequence necessary for a time-series calculation. Finally, ROWS BETWEEN 6 PRECEDING AND CURRENT ROW defines the 7-day window, encompassing the current day's order count and the six previous days' counts to compute the average.

Example and Considerations
This pattern is fundamental for time-series analysis. For large datasets, window functions can be computationally intensive. In distributed systems like Spark, PARTITION BY often triggers a shuffle operation, which can be a significant performance bottleneck. Therefore, it's a best practice to materialize such calculations, perhaps in a dbt model, to optimize downstream queries and dashboard performance.

SELECT
    city_id,
    order_date,
    order_count,
    AVG(order_count) OVER (
        PARTITION BY city_id
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS ma_7d
FROM
    daily_orders;

A key consideration is how data gaps (days with no orders) are handled. The ROWS clause operates on rows, not calendar days. If a city has missing dates, the "7-day" average will technically be an average over the last 7 available order days, not necessarily 7 calendar days.

In the interview, also mention:
Discuss how to handle true calendar-day averages by LEFT JOINing with a complete calendar table and imputing zero order counts for missing dates before applying the window function. This demonstrates a robust understanding of real-world data challenges. You could also briefly touch on the difference between ROWS and RANGE in window definitions.