Python + SQL projects

WITH signed_ledger AS (
  SELECT month, business_unit, cost_centre, account_type,
    CASE WHEN account_type = 'Revenue'
      THEN actual_gbp ELSE -actual_gbp END AS signed_actual,
    CASE WHEN account_type = 'Revenue'
      THEN budget_gbp ELSE -budget_gbp END AS signed_budget,
    CASE WHEN account_type = 'Revenue'
      THEN 0 ELSE -committed_gbp END AS signed_commitment
  FROM ledger
)
SELECT month, business_unit,
  SUM(signed_actual) AS actual_ebitda_gbp,
  SUM(signed_actual - signed_budget) AS variance_gbp,
  SUM(signed_actual + signed_commitment) AS exposed_ebitda_gbp
FROM signed_ledger
GROUP BY month, business_unit;

orders = raw_orders.drop_duplicates("order_id").copy()
orders["channel"] = orders["channel"].fillna("Unknown")
orders = orders.loc[orders["status"].eq("Completed")]

monthly_kpis = pd.read_sql_query("""
  SELECT substr(order_date, 1, 7) AS month,
         COUNT(*) AS completed_orders,
         ROUND(SUM(order_value_gbp), 2) AS revenue_gbp,
         ROUND(AVG(order_value_gbp), 2) AS aov_gbp
  FROM orders
  GROUP BY 1 ORDER BY 1
""", database)

SELECT c.customer_id, c.region, c.segment, c.marketing_source,
       COUNT(o.order_id) AS order_frequency,
       ROUND(COALESCE(SUM(o.order_value_gbp), 0), 2) AS lifetime_value_gbp,
       MAX(o.order_date) AS last_order_date
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.region, c.segment, c.marketing_source;

retention_queue = customer_360[
    (customer_360.order_frequency >= 4)
    & (customer_360.recency_days > 120)
]