> ## Documentation Index
> Fetch the complete documentation index at: https://docs.getparable.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Advanced queries

# Advanced Calendar Queries

Master complex scheduling scenarios, conflict detection, and resource optimization with these advanced SQL patterns for your church calendar.

<Card title="Query Customization Required" icon="circle-exclamation" color="#FFA500" horizontal>
  These example queries demonstrate common patterns but may require adjustments to match your specific database schema and field names. Test thoroughly in your environment before use.
</Card>

## Query Requirements

### Schema Prefix

**IMPORTANT:** All tables in the Planning Center Calendar module live in the `planning_center` schema. Always prefix table names with `planning_center.` in every query.

✅ CORRECT: `SELECT * FROM planning_center.calendar_event_instances`
❌ INCORRECT: `SELECT * FROM calendar_event_instances`

### Row Level Security (RLS)

Row Level Security automatically handles:

* **tenant\_organization\_id** – restricts results to your organization
* **system\_status** – returns active records by default

**Do not add these filters manually**—RLS already enforces them and redundant predicates can hide data or hurt performance:

* ❌ `WHERE tenant_organization_id = 1`
* ❌ `WHERE system_status = 'active'`

Focus on scheduling, resource, and approval logic while trusting RLS to manage tenancy and status.

## Table of Contents

* [Conflict Detection](#conflict-detection)
* [Resource Optimization](#resource-optimization)
* [Complex Scheduling Patterns](#complex-scheduling-patterns)
* [Utilization Analytics](#utilization-analytics)
* [Approval Workflows](#approval-workflows)
* [Recurring Event Management](#recurring-event-management)
* [Performance Optimization](#performance-optimization)

## Conflict Detection

### Find Double-Booked Resources

```sql theme={null}
-- Detect resources with overlapping bookings
WITH booking_conflicts AS (
    SELECT
        rb1.resource_booking_id as booking1_id,
        rb2.resource_booking_id as booking2_id,
        r.name as resource_name,
        r.kind as resource_type,
        rb1.starts_at as booking1_start,
        rb1.ends_at as booking1_end,
        rb2.starts_at as booking2_start,
        rb2.ends_at as booking2_end,
        e1.name as event1_name,
        e2.name as event2_name
    FROM planning_center.calendar_resource_bookings rb1
    JOIN planning_center.calendar_resource_bookings rb2
        ON rb1.resource_id = rb2.resource_id
        AND rb1.resource_booking_id < rb2.resource_booking_id  -- Avoid duplicates
        AND rb1.starts_at < rb2.ends_at  -- Overlap condition
        AND rb1.ends_at > rb2.starts_at
    JOIN planning_center.calendar_resources r
        ON rb1.resource_id = r.resource_id
    JOIN planning_center.calendar_event_instances ei1
        ON rb1.event_instance_id = ei1.event_instance_id
    JOIN planning_center.calendar_events e1
        ON ei1.event_id = e1.event_id
    JOIN planning_center.calendar_event_instances ei2
        ON rb2.event_instance_id = ei2.event_instance_id
    JOIN planning_center.calendar_events e2
        ON ei2.event_id = e2.event_id
    WHERE rb1.starts_at >= CURRENT_DATE  -- Only future conflicts
)
SELECT
    resource_name,
    resource_type,
    event1_name,
    booking1_start,
    booking1_end,
    event2_name,
    booking2_start,
    booking2_end,
    ROUND(
        EXTRACT(EPOCH FROM (
            LEAST(booking1_end, booking2_end) -
            GREATEST(booking1_start, booking2_start)
        ))/3600, 1
    ) as overlap_hours
FROM booking_conflicts
ORDER BY booking1_start, resource_name;
```

### Capacity Violations

```sql theme={null}
-- Find bookings exceeding resource capacity
WITH resource_capacity_check AS (
    SELECT
        rb.starts_at,
        rb.ends_at,
        r.resource_id,
        r.name as resource_name,
        r.quantity as max_capacity,
        SUM(rb.quantity) OVER (
            PARTITION BY r.resource_id, rb.starts_at
            ORDER BY rb.starts_at
        ) as total_booked,
        e.name as event_name
    FROM planning_center.calendar_resource_bookings rb
    JOIN planning_center.calendar_resources r ON rb.resource_id = r.resource_id
    JOIN planning_center.calendar_event_instances ei ON rb.event_instance_id = ei.event_instance_id
    JOIN planning_center.calendar_events e ON ei.event_id = e.event_id
    WHERE rb.starts_at >= CURRENT_DATE
)
SELECT
    resource_name,
    max_capacity,
    total_booked,
    total_booked - max_capacity as overbooked_by,
    starts_at,
    event_name
FROM resource_capacity_check
WHERE total_booked > max_capacity
ORDER BY starts_at, resource_name;
```

### Time Buffer Violations

```sql theme={null}
-- Find back-to-back bookings without buffer time
WITH sequential_bookings AS (
    SELECT
        r.name as resource_name,
        e1.name as first_event,
        rb1.ends_at as first_ends,
        e2.name as second_event,
        rb2.starts_at as second_starts,
        ROUND(
            EXTRACT(EPOCH FROM (rb2.starts_at - rb1.ends_at))/60, 1
        ) as gap_minutes
    FROM planning_center.calendar_resource_bookings rb1
    JOIN planning_center.calendar_resource_bookings rb2
        ON rb1.resource_id = rb2.resource_id
        AND rb2.starts_at >= rb1.ends_at
        AND rb2.starts_at < rb1.ends_at + INTERVAL '30 minutes'  -- Within 30 min
    JOIN planning_center.calendar_resources r ON rb1.resource_id = r.resource_id
    JOIN planning_center.calendar_event_instances ei1 ON rb1.event_instance_id = ei1.event_instance_id
    JOIN planning_center.calendar_events e1 ON ei1.event_id = e1.event_id
    JOIN planning_center.calendar_event_instances ei2 ON rb2.event_instance_id = ei2.event_instance_id
    JOIN planning_center.calendar_events e2 ON ei2.event_id = e2.event_id
    WHERE rb1.starts_at >= CURRENT_DATE
)
SELECT *
FROM sequential_bookings
WHERE gap_minutes < 15  -- Less than 15 minute buffer
ORDER BY first_ends;
```

## Resource Optimization

### Underutilized Resources

```sql theme={null}
-- Find resources rarely used
  WITH resource_usage AS (
      SELECT
          r.resource_id,
          r.name,
          r.kind,
          COUNT(rb.resource_booking_id) as booking_count,
          COALESCE(
              SUM(EXTRACT(EPOCH FROM (rb.ends_at - rb.starts_at))/3600),
              0
          ) as total_hours_booked,
          -- Only consider past bookings for "last used" date
          MAX(CASE
              WHEN rb.starts_at <= CURRENT_DATE THEN rb.starts_at
              ELSE NULL
          END) as last_booking_date,
          -- Track future bookings separately
          MIN(CASE
              WHEN rb.starts_at > CURRENT_DATE THEN rb.starts_at
              ELSE NULL
          END) as next_booking_date
      FROM planning_center.calendar_resources r
      LEFT JOIN planning_center.calendar_resource_bookings rb
          ON r.resource_id = rb.resource_id
          AND rb.starts_at >= CURRENT_DATE - INTERVAL '90 days'
      GROUP BY r.resource_id, r.name, r.kind
  ),
  usage_stats AS (
      SELECT
          *,
          total_hours_booked / (90 * 24.0) * 100 as utilization_percentage,
          CASE
              WHEN last_booking_date IS NOT NULL
              THEN CURRENT_DATE - last_booking_date::date
              ELSE NULL
          END as days_since_last_booking
      FROM resource_usage
  )
  SELECT
      name,
      kind,
      booking_count,
      ROUND(total_hours_booked, 2) as hours_used_90_days,
      ROUND(utilization_percentage, 2) as utilization_pct,
      CASE
          WHEN days_since_last_booking IS NOT NULL
          THEN days_since_last_booking::text || ' days ago'
          WHEN next_booking_date IS NOT NULL
          THEN 'Scheduled for ' || next_booking_date::date::text
          ELSE 'Never booked'
      END as last_activity
  FROM usage_stats
  WHERE utilization_percentage < 10  -- Less than 10% utilized
     OR booking_count < 5  -- Or rarely booked
  ORDER BY utilization_percentage;
```

### Peak Usage Times

```sql theme={null}
-- Identify when resources are most in demand
WITH hourly_usage AS (
    SELECT
        EXTRACT(DOW FROM rb.starts_at) as day_of_week,
        EXTRACT(HOUR FROM rb.starts_at) as hour_of_day,
        TO_CHAR(rb.starts_at, 'Day') as day_name,
        COUNT(DISTINCT rb.resource_booking_id) as bookings,
        COUNT(DISTINCT rb.resource_id) as unique_resources
    FROM planning_center.calendar_resource_bookings rb
    WHERE rb.starts_at >= CURRENT_DATE - INTERVAL '180 days'
    GROUP BY
        EXTRACT(DOW FROM rb.starts_at),
        EXTRACT(HOUR FROM rb.starts_at),
        TO_CHAR(rb.starts_at, 'Day')
)
SELECT
    day_name,
    hour_of_day || ':00' as time_slot,
    bookings,
    unique_resources,
    REPEAT('█', (bookings::float / MAX(bookings) OVER () * 20)::int) as usage_bar
FROM hourly_usage
WHERE bookings > 0
ORDER BY day_of_week, hour_of_day;
```

### Optimal Resource Allocation

```sql theme={null}
-- Suggest resource reassignments based on usage patterns
WITH resource_demand AS (
    SELECT
        DATE_TRUNC('week', rb.starts_at) as week,
        r.kind,
        COUNT(DISTINCT rb.resource_booking_id) as bookings,
        COUNT(DISTINCT r.resource_id) as resources_used,
        SUM(rb.quantity) as total_quantity_requested
    FROM planning_center.calendar_resource_bookings rb
    JOIN planning_center.calendar_resources r ON rb.resource_id = r.resource_id
    WHERE rb.starts_at >= CURRENT_DATE - INTERVAL '12 weeks'
    GROUP BY DATE_TRUNC('week', rb.starts_at), r.kind
),
resource_availability AS (
    SELECT
        kind,
        COUNT(*) as total_resources,
        SUM(quantity) as total_capacity
    FROM planning_center.calendar_resources
    GROUP BY kind
)
SELECT
    rd.kind as resource_type,
    ROUND(AVG(rd.bookings)::numeric, 2) as avg_weekly_bookings,
    ROUND(AVG(rd.resources_used)::numeric, 2) as avg_resources_used,
    ra.total_resources as available_resources,
    ROUND(AVG(rd.resources_used) * 100.0 / ra.total_resources, 2) as utilization_rate,
    CASE
        WHEN AVG(rd.resources_used) * 100.0 / ra.total_resources > 80 THEN 'High Demand - Consider adding resources'
        WHEN AVG(rd.resources_used) * 100.0 / ra.total_resources < 30 THEN 'Low Demand - Consider consolidating'
        ELSE 'Balanced'
    END as recommendation
FROM resource_demand rd
JOIN resource_availability ra ON rd.kind = ra.kind
GROUP BY rd.kind, ra.total_resources, ra.total_capacity
ORDER BY utilization_rate DESC;
```

## Complex Scheduling Patterns

### Multi-Resource Event Requirements

```sql theme={null}
-- Events requiring multiple resources simultaneously
WITH event_resource_summary AS (
    SELECT
        e.event_id,
        e.name as event_name,
        ei.starts_at,
        ei.ends_at,
        COUNT(DISTINCT rb.resource_id) as resource_count,
        STRING_AGG(DISTINCT r.name || ' (' || r.kind || ')', ', ' ORDER BY r.name) as resources_needed,
        SUM(rb.quantity) as total_quantity
    FROM planning_center.calendar_events e
    JOIN planning_center.calendar_event_instances ei ON e.event_id = ei.event_id
    JOIN planning_center.calendar_resource_bookings rb ON ei.event_instance_id = rb.event_instance_id
    JOIN planning_center.calendar_resources r ON rb.resource_id = r.resource_id
    WHERE ei.starts_at >= CURRENT_DATE
    GROUP BY e.event_id, e.name, ei.event_instance_id, ei.starts_at, ei.ends_at
    HAVING COUNT(DISTINCT rb.resource_id) > 1  -- Multiple resources
)
SELECT
    event_name,
    starts_at,
    resource_count,
    resources_needed,
    total_quantity
FROM event_resource_summary
ORDER BY starts_at, resource_count DESC;
```

### Recurring Event Pattern Analysis

```sql theme={null}
-- Analyze recurring event patterns and exceptions
WITH recurring_analysis AS (
    SELECT
        e.event_id,
        e.name,
        ei.recurrence,
        ei.recurrence_description,
        COUNT(*) as total_instances,
        MIN(ei.starts_at) as first_occurrence,
        MAX(ei.starts_at) as last_occurrence,
        -- Calculate average interval between occurrences
        EXTRACT(EPOCH FROM (MAX(ei.starts_at) - MIN(ei.starts_at))) /
            NULLIF(COUNT(*) - 1, 0) / 86400 as avg_days_between,
        -- Detect irregular patterns
        STDDEV(EXTRACT(DOW FROM ei.starts_at)) as day_variance,
        STDDEV(EXTRACT(HOUR FROM ei.starts_at)) as hour_variance
    FROM planning_center.calendar_events e
    JOIN planning_center.calendar_event_instances ei ON e.event_id = ei.event_id
    WHERE ei.recurrence IS NOT NULL
    GROUP BY e.event_id, e.name, ei.recurrence, ei.recurrence_description
)
SELECT
    name,
    recurrence_description,
    total_instances,
    TO_CHAR(first_occurrence, 'Mon DD, YYYY') as first_date,
    TO_CHAR(last_occurrence, 'Mon DD, YYYY') as last_date,
    ROUND(avg_days_between, 1) as avg_days_interval,
    CASE
        WHEN day_variance < 0.5 THEN 'Consistent day'
        WHEN day_variance < 2 THEN 'Variable day'
        ELSE 'Irregular schedule'
    END as schedule_consistency,
    CASE
        WHEN hour_variance < 0.5 THEN 'Consistent time'
        ELSE 'Variable time'
    END as time_consistency
FROM recurring_analysis
ORDER BY total_instances DESC;
```

### Availability Windows

```sql theme={null}
-- Find available time slots for a resource
WITH time_slots AS (
    -- Generate hourly time slots for next 7 days
    SELECT
        generate_series(
            DATE_TRUNC('hour', CURRENT_TIMESTAMP),
            DATE_TRUNC('hour', CURRENT_TIMESTAMP) + INTERVAL '7 days',
            INTERVAL '1 hour'
        ) as slot_start
),
booked_slots AS (
    -- Find already booked time slots
    SELECT DISTINCT
        DATE_TRUNC('hour', rb.starts_at) as booked_start,
        DATE_TRUNC('hour', rb.ends_at) + INTERVAL '1 hour' as booked_end,
        r.resource_id,
        r.name
    FROM planning_center.calendar_resource_bookings rb
    JOIN planning_center.calendar_resources r ON rb.resource_id = r.resource_id
    WHERE r.name = 'Main Sanctuary'  -- Change to desired resource
        AND rb.starts_at >= CURRENT_TIMESTAMP
        AND rb.starts_at < CURRENT_TIMESTAMP + INTERVAL '7 days'
),
availability AS (
    SELECT
        ts.slot_start,
        ts.slot_start + INTERVAL '1 hour' as slot_end,
        CASE
            WHEN bs.booked_start IS NULL THEN 'Available'
            ELSE 'Booked'
        END as status
    FROM time_slots ts
    LEFT JOIN booked_slots bs
        ON ts.slot_start >= bs.booked_start
        AND ts.slot_start < bs.booked_end
    WHERE EXTRACT(HOUR FROM ts.slot_start) BETWEEN 8 AND 20  -- Business hours only
)
SELECT
    TO_CHAR(slot_start, 'Day, Mon DD') as date,
    TO_CHAR(slot_start, 'HH12:MI AM') || ' - ' || TO_CHAR(slot_end, 'HH12:MI AM') as time_slot,
    status
FROM availability
WHERE status = 'Available'
ORDER BY slot_start
LIMIT 20;
```

## Utilization Analytics

### Monthly Utilization Trends

```sql theme={null}
-- Track facility utilization trends over time
WITH monthly_metrics AS (
    SELECT
        DATE_TRUNC('month', rb.starts_at) as month,
        r.kind as resource_type,
        COUNT(DISTINCT rb.resource_booking_id) as total_bookings,
        COUNT(DISTINCT DATE(rb.starts_at)) as days_with_bookings,
        COUNT(DISTINCT rb.resource_id) as unique_resources_used,
        SUM(EXTRACT(EPOCH FROM (rb.ends_at - rb.starts_at))/3600) as total_hours,
        AVG(EXTRACT(EPOCH FROM (rb.ends_at - rb.starts_at))/3600) as avg_booking_hours
    FROM planning_center.calendar_resource_bookings rb
    JOIN planning_center.calendar_resources r ON rb.resource_id = r.resource_id
    WHERE rb.starts_at >= CURRENT_DATE - INTERVAL '12 months'
    GROUP BY DATE_TRUNC('month', rb.starts_at), r.kind
),
with_trends AS (
    SELECT
        *,
        LAG(total_bookings, 1) OVER (PARTITION BY resource_type ORDER BY month) as prev_month_bookings,
        AVG(total_hours) OVER (
            PARTITION BY resource_type
            ORDER BY month
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) as three_month_avg_hours
    FROM monthly_metrics
)
SELECT
    TO_CHAR(month, 'Mon YYYY') as month_year,
    resource_type,
    total_bookings,
    days_with_bookings,
    ROUND(total_hours, 1) as total_hours,
    ROUND(avg_booking_hours, 1) as avg_duration,
    ROUND(((total_bookings - prev_month_bookings) * 100.0 / NULLIF(prev_month_bookings, 0)), 1) as month_over_month_pct,
    ROUND(three_month_avg_hours, 1) as rolling_3mo_avg_hours
FROM with_trends
ORDER BY month DESC, resource_type;
```

### Cost Per Use Analysis

```sql theme={null}
-- Calculate implied cost per resource use (if costs were tracked)
WITH resource_usage_costs AS (
    SELECT
        r.resource_id,
        r.name,
        r.kind,
        COUNT(rb.resource_booking_id) as times_used,
        SUM(EXTRACT(EPOCH FROM (rb.ends_at - rb.starts_at))/3600) as total_hours_used,
        -- Assuming some baseline costs (customize as needed)
        CASE r.kind
            WHEN 'Room' THEN 50  -- $50/hour for rooms
            WHEN 'Equipment' THEN 25  -- $25/hour for equipment
            ELSE 10  -- $10/hour for other
        END as hourly_rate
    FROM planning_center.calendar_resources r
    LEFT JOIN planning_center.calendar_resource_bookings rb
        ON r.resource_id = rb.resource_id
        AND rb.starts_at >= DATE_TRUNC('month', CURRENT_DATE)
    GROUP BY r.resource_id, r.name, r.kind
)
SELECT
    name,
    kind,
    times_used,
    ROUND(total_hours_used, 2) as hours_used,
    hourly_rate as rate_per_hour,
    ROUND(total_hours_used * hourly_rate, 2) as implied_value,
    CASE
        WHEN times_used > 0 THEN ROUND((total_hours_used * hourly_rate) / times_used, 2)
        ELSE 0
    END as value_per_use
FROM resource_usage_costs
WHERE times_used > 0
ORDER BY implied_value DESC;
```

## Approval Workflows

### Pending Approvals

```sql theme={null}
-- Events awaiting approval with their resource requirements
SELECT
    e.event_id,
    e.name as event_name,
    e.approval_status,
    e.percent_approved,
    e.percent_rejected,
    ei.starts_at,
    COUNT(DISTINCT err.event_resource_request_id) as pending_resource_requests,
    STRING_AGG(DISTINCT r.name, ', ') as requested_resources
FROM planning_center.calendar_events e
JOIN planning_center.calendar_event_instances ei ON e.event_id = ei.event_id
LEFT JOIN planning_center.calendar_event_resource_requests err
    ON e.event_id = err.event_id
LEFT JOIN planning_center.calendar_resources r
    ON err.resource_id = r.resource_id
WHERE e.approval_status IN ('P', NULL)  -- Pending or not yet reviewed
    AND ei.starts_at >= CURRENT_DATE
GROUP BY e.event_id, e.name, e.approval_status, e.percent_approved,
         e.percent_rejected, ei.event_instance_id, ei.starts_at
ORDER BY ei.starts_at;
```

### Approval Response Times

```sql theme={null}
-- Analyze how quickly approvals are processed
WITH approval_metrics AS (
    SELECT
        e.event_id,
        e.name,
        e.created_at as request_time,
        e.updated_at as decision_time,
        e.approval_status,
        EXTRACT(EPOCH FROM (e.updated_at - e.created_at))/3600 as hours_to_decision,
        ei.starts_at as event_start,
        EXTRACT(EPOCH FROM (ei.starts_at - e.created_at))/86400 as days_advance_notice
    FROM planning_center.calendar_events e
    JOIN planning_center.calendar_event_instances ei ON e.event_id = ei.event_id
    WHERE e.approval_status IS NOT NULL
        AND e.updated_at > e.created_at
)
SELECT
    approval_status,
    COUNT(*) as total_events,
    ROUND(AVG(hours_to_decision), 1) as avg_hours_to_decision,
    ROUND(MIN(hours_to_decision), 1) as fastest_decision,
    ROUND(MAX(hours_to_decision), 1) as slowest_decision,
    ROUND(AVG(days_advance_notice), 1) as avg_days_advance_notice
FROM approval_metrics
GROUP BY approval_status
ORDER BY approval_status;
```

## Recurring Event Management

### Detect Broken Recurring Patterns

```sql theme={null}
-- Find recurring events with missed occurrences
WITH expected_intervals AS (
    SELECT
        e.event_id,
        e.name,
        ei.recurrence,
        -- Calculate expected interval based on recurrence type
        CASE
            WHEN ei.recurrence LIKE '%WEEKLY%' THEN 7
            WHEN ei.recurrence LIKE '%DAILY%' THEN 1
            WHEN ei.recurrence LIKE '%MONTHLY%' THEN 30
            ELSE NULL
        END as expected_days,
        ei.starts_at,
        LAG(ei.starts_at) OVER (PARTITION BY e.event_id ORDER BY ei.starts_at) as prev_occurrence,
        EXTRACT(EPOCH FROM (
            ei.starts_at - LAG(ei.starts_at) OVER (PARTITION BY e.event_id ORDER BY ei.starts_at)
        ))/86400 as actual_days_gap
    FROM planning_center.calendar_events e
    JOIN planning_center.calendar_event_instances ei ON e.event_id = ei.event_id
    WHERE ei.recurrence IS NOT NULL
)
SELECT
    name,
    TO_CHAR(prev_occurrence, 'Mon DD, YYYY') as previous_date,
    TO_CHAR(starts_at, 'Mon DD, YYYY') as current_date,
    expected_days,
    ROUND(actual_days_gap, 1) as actual_days,
    ROUND(actual_days_gap - expected_days, 1) as variance_days
FROM expected_intervals
WHERE expected_days IS NOT NULL
    AND actual_days_gap IS NOT NULL
    AND ABS(actual_days_gap - expected_days) > expected_days * 0.2  -- 20% variance
ORDER BY starts_at DESC;
```

## Performance Optimization

### Optimized Conflict Detection Query

```sql theme={null}
-- Efficient conflict detection using window functions
WITH resource_timeline AS (
    SELECT
        rb.resource_id,
        r.name as resource_name,
        rb.starts_at,
        rb.ends_at,
        e.name as event_name,
        -- Use window functions to find overlaps
        LAG(rb.ends_at) OVER (PARTITION BY rb.resource_id ORDER BY rb.starts_at) as prev_end,
        LEAD(rb.starts_at) OVER (PARTITION BY rb.resource_id ORDER BY rb.starts_at) as next_start
    FROM planning_center.calendar_resource_bookings rb
    JOIN planning_center.calendar_resources r ON rb.resource_id = r.resource_id
    JOIN planning_center.calendar_event_instances ei ON rb.event_instance_id = ei.event_instance_id
    JOIN planning_center.calendar_events e ON ei.event_id = e.event_id
    WHERE rb.starts_at >= CURRENT_DATE
        AND rb.starts_at < CURRENT_DATE + INTERVAL '30 days'
)
SELECT
    resource_name,
    event_name,
    starts_at,
    ends_at,
    CASE
        WHEN prev_end > starts_at THEN 'Conflict with previous'
        WHEN next_start < ends_at THEN 'Conflict with next'
        ELSE 'No conflict'
    END as conflict_status
FROM resource_timeline
WHERE prev_end > starts_at OR next_start < ends_at
ORDER BY resource_id, starts_at;
```

## Best Practices for Advanced Calendar Queries

### 1. Use Window Functions for Sequential Analysis

Window functions are perfect for finding gaps, overlaps, and patterns in scheduling data.

### 2. Optimize Date Range Filters

Always filter by date ranges early in your query to reduce the dataset size.

### 3. Handle NULL Values in Scheduling

Remember that some fields like approval\_status or recurrence might be NULL.

### 4. Consider Time Zones

Ensure your timestamp comparisons account for time zone differences if applicable.

### 5. Use CTEs for Complex Logic

Break down complex scheduling logic into manageable CTEs for better readability and performance.

## Next Steps

* Review [Reporting Examples](/planning-center/calendar/reporting-examples) for complete, production-ready reports
* Check the [Data Model](/planning-center/calendar/data-model) for detailed table documentation
* Return to [Basic Queries](/planning-center/calendar/basic-queries) to review fundamentals