Weekend vs Weekday Sales — Solution & ExplanationProblems / Weekend vs Weekday Sales / EditorialApproach

Classify each sale. CASE WHEN dayofweek(sale_date) IN (0, 6) THEN 'weekend' ELSE 'weekday' END — in DuckDB, dayofweek returns 0 for Sunday and 6 for Saturday, so those two values are the weekend.

Aggregate per group. SUM(amount) and AVG(amount) per day_type, both wrapped in ROUND(..., 2).

Order by day_type so weekday comes before weekend.

Key InsightEvery engine numbers weekdays differently, and this is a classic source of silent bugs: DuckDB dayofweek is 0=Sunday..6=Saturday, Spark F.dayofweek is 1=Sunday..7=Saturday, pandas .dt.dayofweek is 0=Monday..6=Sunday, and ISO isodow is 1=Monday..7=Sunday. The same predicate IN (0, 6) that means weekend in DuckDB means Sunday-and-Friday in pandas. When porting date logic across engines, always re-derive the weekend condition from the engine's own convention instead of copying the numbers.Hints

This is a CASE expression over the day of week, followed by a two-group aggregation.

Day-of-week numbering is NOT portable: DuckDB's dayofweek() gives 0 = Sunday .. 6 = Saturday (weekend = 0 or 6), Spark's F.dayofweek() gives 1 = Sunday .. 7 = Saturday (weekend = 1 or 7), and pandas .dt.dayofweek gives 0 = Monday .. 6 = Sunday (weekend >= 5).

GROUP BY the derived day_type label, then SUM and AVG the amount; round both to 2 decimal places.

Solve Weekend vs Weekday Sales yourself →