Bool Flags to Categories — Solution & ExplanationProblems / Bool Flags to Categories / EditorialApproach

Fold the flags with a CASE ladder: CASE WHEN is_mobile THEN 'mobile' WHEN is_desktop THEN 'desktop' WHEN is_tablet THEN 'tablet' END. Booleans are already conditions, so no = TRUE comparison is needed. Since the flags are one-hot (exactly one true per row), the branch order is irrelevant and every row lands in exactly one bucket.

GROUP BY the derived column and COUNT(*) the rows in each bucket. DuckDB allows grouping by the select-list alias device_type; in stricter engines you would repeat the CASE expression in the GROUP BY or derive the column in a subquery/CTE first.

Order by device_type.

Key InsightOne-hot flag columns are a *wide* (pivoted) encoding of a categorical value, common in ML feature exports and survey data. Collapsing them back — sometimes called reverse pivoting or unpivoting — is just a CASE ladder, after which ordinary GROUP BY analytics work again. The one thing to verify before trusting the ladder is the one-hot invariant itself: if two flags could both be true, the first matching WHEN silently wins, and if none could be true, you'd need an ELSE bucket. Checking is_mobile + is_desktop + is_tablet = 1 per row is a cheap data-quality assertion when the invariant matters.Hints

This is the reverse of a pivot: three one-hot columns collapse back into one categorical column. A CASE ladder does the folding.

Boolean columns need no comparison — CASE WHEN is_mobile THEN 'mobile' works directly (writing is_mobile = TRUE is redundant).

Because exactly one flag is true per row, the WHEN order doesn't change the answer, and the last branch can even be an ELSE. Then GROUP BY the derived device_type and COUNT(*). DuckDB lets you GROUP BY the alias; in engines that don't, repeat the CASE expression or wrap it in a subquery.

In PySpark: .withColumn("device_type", F.when(F.col("is_mobile"), F.lit("mobile")).when(...).otherwise(...)) then .groupBy("device_type").agg(...).

Solve Bool Flags to Categories yourself →