Parking Reservation Reporting Database

Core Tables

spot_state_combined
Archive and snapshot of parking spot occupancy and reservation states segmented by time intervals for past and future dates. Contains key fields: day, from_second, to_second, spot_key, user_id_hash, parking_id_hash.

facility_users
Maps users to groups within facilities as part of archived and snapshot user-group relations tied to reservations. Contains key fields: user_id_hash, parking_id_hash, group_id.

facility_users_details_current
Current user info for joining historical records with user details. Contains key fields: user_id_hash, user_name, contact_info.

facility_groups
Defines groups within each parking facility. Contains key fields: group_id, parking_id_hash, group_name.

parking_name
Maps facility ID to human-readable name. Contains key fields: parking_id_hash, facility_name.

📋 Example Queries

1. Daily Occupancy Rate

SELECT
  strftime('%Y-%m-%d', datetime(day * 86400, 'unixepoch')) AS date,
  COUNT(CASE WHEN user_id_hash IS NOT NULL THEN 1 END) * 1.0 / COUNT(*) * 100 AS occupancy_percent
FROM spot_state_combined
WHERE day BETWEEN {{from_day}} AND {{to_day}}
GROUP BY day
ORDER BY date;

2. Top 5 Users by Total Hours Reserved

SELECT
  user_name,
  SUM((to_second - from_second) / 3600.0) AS total_hours
FROM spot_state_combined
WHERE day BETWEEN {{from_day}} AND {{to_day}}
  AND user_id_hash IS NOT NULL
GROUP BY user_name
ORDER BY total_hours DESC
LIMIT 5;

3. Most Frequently Reserved Spots

SELECT
  spot_name,
  COUNT(*) AS reservation_count,
  SUM((to_second - from_second) / 3600.0) AS total_hours_reserved
FROM spot_state_combined
WHERE day BETWEEN {{from_day}} AND {{to_day}}
  AND user_id_hash IS NOT NULL
GROUP BY spot_key, spot_name
ORDER BY reservation_count DESC;

4. Upcoming Reservations (Next 7 Days)

SELECT
  strftime('%Y-%m-%d %H:%M', datetime(day * 86400 + from_second, 'unixepoch')) AS start_time,
  strftime('%Y-%m-%d %H:%M', datetime(day * 86400 + to_second, 'unixepoch')) AS end_time,
  spot_name,
  COALESCE(user_name, '-') AS reserved_by
FROM spot_state_combined
WHERE day BETWEEN CAST(strftime('%s','now')/86400 AS INT) 
              AND CAST(strftime('%s','now')/86400 AS INT) + 7
ORDER BY start_time;

5. Free Spots Right Now

SELECT
  spot_name
FROM spot_state_combined
WHERE day = CAST(strftime('%s','now')/86400 AS INT)
  AND from_second <= strftime('%s','now') % 86400
  AND to_second > strftime('%s','now') % 86400
  AND user_id_hash IS NULL;

6. Group Usage Trend Over Time

SELECT
  g.group_name,
  strftime('%Y-%m-%d', datetime(s.day * 86400, 'unixepoch')) AS date,
  SUM((s.to_second - s.from_second)/3600.0) AS total_hours
FROM spot_state_combined s
JOIN facility_users u ON u.user_id_hash = s.user_id_hash AND u.parking_id_hash = s.parking_id_hash
JOIN facility_groups g ON g.group_id = u.group_id AND g.parking_id_hash = s.parking_id_hash
WHERE s.day BETWEEN {{from_day}} AND {{to_day}}
  AND s.user_id_hash IS NOT NULL
GROUP BY g.group_name, s.day
ORDER BY date, g.group_name;

How to create custom reports

Parking Reservation Reporting Database

Core Tables

📋 Example Queries