Apache Spark Python - Transformations - Solution - Get Daily Revenue rolled up till Yearly

In this article, you will learn how to use Spark to get the number of orders and revenue for each year, month, and date by filtering orders that are either COMPLETE or CLOSED. The data will be rolled up at the daily, monthly, and yearly levels, providing a comprehensive analysis of the orders and revenue. Follow the step-by-step instructions and code examples to understand and implement the process.

Filter Orders:

Filter the orders dataset to include only orders that are marked as COMPLETE or CLOSED.

orders_filtered = orders.filter("order_status IN ('COMPLETE', 'CLOSED')")

Join Data:

Join the filtered orders dataset with the order_items dataset based on the order_id.

orders_join = orders_filtered.join(order_items, orders_filtered.order_id == order_items.order_item_order_id)

Rollup and Aggregate:

Roll up the data by year, month, and date to get the count of orders and total revenue for each day.

revenue = orders_join.rollup(year('order_date').alias('order_year'), date_format(col('order_date'), 'yyyyMM').alias('order_month'), 'order_date').agg(countDistinct('order_id').alias('order_count'), round(sum('order_item_subtotal'), 2).alias('revenue')).orderBy('order_year', 'order_month', 'order_date')

Please follow the tutorial for the step-by-step instructions on how to solve this problem using Spark.

Watch the video tutorial here

Hands-On Tasks:

  1. Filter orders to include only COMPLETE or CLOSED orders.
  2. Join orders with order_items based on order_id.
  3. Roll up data by year, month, and date to get the order count and total revenue.
  4. Sort the data by year, month, and date.


In conclusion, this article has demonstrated how to efficiently analyze the number of orders and revenue for each year, month, and date using Spark. By following the provided instructions and examples, you can replicate the process and gain valuable insights from your data analysis. Practice the tasks and engage with the community for further learning and collaboration.