Conditional Aggregations per Group

How do we count successful and failed logins separately per account using conditional when() expressions?

Intermediate⏱ 3 Minutes⭐ 460 XP

🤔 THE QUESTION

How do we count successful and failed logins separately per account using conditional when() expressions?

💡 WHAT IS IT?

Nesting when() inside count() to compute pivot-like metrics for distinct status conditions per group.

🎯 WHAT IS IT USED FOR?

Security threat detection, fraud monitoring, and user authentication health tracking.

💻 EXAMPLE

from pyspark.sql.functions import col, count, when

df.groupBy("account_id").agg(
    count(when(col("status") == "SUCCESS", 1)).alias("successful_logins"),
    count(when(col("status") == "FAILURE", 1)).alias("failed_logins")
).show()

🎯 Mission Objectives

Practice typing production-grade PySpark code for Conditional Aggregations per Group.

✓ Nest when() inside count()
✓ Compute success and failure counts per account
✓ Enable security anomaly detection

🚀 Start Mission

← Prev (Custom Multi-Metric Aggregations with agg())Next Lesson (Business Dimension Summary Pipeline) →