Scenario: Daily Financial Revenue Reconciliation

How can we reconcile gateway processing fees, gross sales, refunds, and net revenue across payment providers daily?

Production⏱ 3 Minutes⭐ 820 XP

🤔 THE QUESTION

How can we reconcile gateway processing fees, gross sales, refunds, and net revenue across payment providers daily?

💡 WHAT IS IT?

Conditional sum() aggregations by date and provider calculate financial balances and flag reconciliation discrepancies.

🎯 WHAT IS IT USED FOR?

Automated accounting pipelines verifying that bank deposits match internal ledger records exactly.

💻 EXAMPLE

df_recon = df_ledger.groupBy("transaction_date", "payment_provider").agg(sum(when(col("type") == "SALE", col("amount")).otherwise(0)).alias("gross_sales"), sum(when(col("type") == "REFUND", col("amount")).otherwise(0)).alias("total_refunds"), sum("processing_fee").alias("total_fees")).withColumn("net_revenue", col("gross_sales") - col("total_refunds") - col("total_fees"))

🎯 Mission Objectives

Practice typing production-grade PySpark code for Scenario: Daily Financial Revenue Reconciliation.

✓ Financial ledger reconciliation
✓ Conditional fee and refund math
✓ Net revenue balance check

🚀 Start Mission

← Prev (Scenario: Real-Time Fraud Event Detection)Next Lesson (Scenario: E-Commerce Funnel Conversion Analytics) →