How would you analyze attendance trends to determine if more people attend the office on weekdays versus holidays, given that attendance counts are greater than 50?

To analyze attendance trends, I would first normalize the data by creating a dimension table that classifies each date as a weekday, weekend, or holiday. Then, using the attendance fact table, I would write a SQL query that groups by the date type and calculates the average attendance count and the percentage of days where attendance exceeds 50%. For example:

WITH date_type AS (
  SELECT date, 
         CASE 
           WHEN DAYOFWEEK(date) IN (1,7) THEN 'Weekend'
           WHEN is_holiday = 1 THEN 'Holiday'
           ELSE 'Weekday'
         END AS type
  FROM calendar
),
attendance AS (
  SELECT a.date, a.count
  FROM attendance a
  JOIN date_type d ON a.date = d.date
)
SELECT type,
       COUNT(*) AS total_days,
       AVG(count) AS avg_attendance,
       SUM(CASE WHEN count > 50 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS pct_above_50
FROM attendance
GROUP BY type;

This query gives the average attendance and the proportion of days with attendance above 50% for each date type. By comparing the results, we can see whether weekdays or holidays have higher attendance. Complexity is O(n) over the attendance rows, and the query can be optimized with proper indexing on the date columns.