> ## 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.

# Basic queries

# Basic People Queries

Start with these foundational queries to explore your church database. Each example builds your SQL confidence while providing immediate ministry value.

<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 People module live in the `planning_center` schema. Always prefix table names with `planning_center.` in every query.

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

### Row Level Security (RLS)

Row Level Security automatically filters results for:

* **tenant\_organization\_id** – only data from your organization
* **system\_status** – only active records by default

**Do not add these filters manually**—RLS applies them for you and redundant conditions can slow queries or hide data:

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

Trust the built-in RLS policies to keep your results scoped correctly while you focus on ministry-specific filters.

## Table of Contents

* [Finding People](#finding-people)
* [Understanding Demographics](#understanding-demographics)
* [Working with Households](#working-with-households)
* [Contact Information](#contact-information)
* [Membership and Status](#membership-and-status)
* [Date-Based Queries](#date-based-queries)
* [Basic Statistics](#basic-statistics)

## Finding People

### List Active People

```sql theme={null}
-- View active people in your database
SELECT 
    person_id,
    first_name,
    last_name,
    name,
    status,
    membership,
    created_at
FROM planning_center.people_people
WHERE status = 'active'
ORDER BY last_name, first_name
LIMIT 100;
```

### Search by Name

```sql theme={null}
-- Find people by name (case-insensitive)
SELECT 
    person_id,
    name,
    nickname,
    status,
    membership,
    birthdate
FROM planning_center.people_people
WHERE status = 'active'
  AND (
    LOWER(first_name) LIKE '%sarah%'
    OR LOWER(last_name) LIKE '%johnson%'
    OR LOWER(nickname) LIKE '%sarah%'
  )
ORDER BY last_name, first_name;
```

### Recent Additions

```sql theme={null}
-- People added in the last 30 days
SELECT 
    person_id,
    name,
    status,
    membership,
    created_at,
    CURRENT_DATE - created_at::date as days_since_added
FROM planning_center.people_people
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
  AND status = 'active'
ORDER BY created_at DESC;
```

### Find by Person ID

```sql theme={null}
-- Look up a specific person
SELECT 
    person_id,
    name,
    first_name,
    last_name,
    nickname,
    birthdate,
    anniversary,
    gender,
    membership,
    status,
    child,
    grade,
    graduation_year
FROM planning_center.people_people
WHERE person_id = 'YOUR_PERSON_ID'
  AND status = 'active';
```

## Understanding Demographics

### Age Distribution

```sql theme={null}
-- Calculate ages and group people
SELECT 
    person_id,
    name,
    birthdate,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) as age,
    CASE 
        WHEN child = true THEN 'Child'
        WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) < 18 THEN 'Youth'
        WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) < 30 THEN 'Young Adult'
        WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) < 50 THEN 'Adult'
        WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) < 65 THEN 'Middle Age'
        ELSE 'Senior'
    END as age_group
FROM planning_center.people_people
WHERE birthdate IS NOT NULL
  AND status = 'active'
ORDER BY birthdate;
```

### Children and Students

```sql theme={null}
-- Find all children and students
SELECT 
    person_id,
    name,
    birthdate,
    grade,
    graduation_year,
    school_type,
    CASE 
        WHEN grade IS NOT NULL THEN 'Grade ' || grade::text
        WHEN graduation_year IS NOT NULL THEN 'Grad Year ' || graduation_year::text
        WHEN child = true THEN 'Child'
        ELSE 'Unknown'
    END as education_status
FROM planning_center.people_people
WHERE (child = true OR grade IS NOT NULL OR graduation_year IS NOT NULL)
  AND status = 'active'
ORDER BY grade, graduation_year, name;
```

### Gender Breakdown

```sql theme={null}
-- Analyze gender distribution
SELECT 
    gender,
    COUNT(*) as count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) as percentage
FROM planning_center.people_people
WHERE status = 'active'
GROUP BY gender
ORDER BY count DESC;
```

### Upcoming Birthdays

```sql theme={null}
-- Birthdays in the next 30 days
SELECT 
    person_id,
    name,
    birthdate,
    TO_CHAR(birthdate, 'Month DD') as birthday,
    EXTRACT(YEAR FROM AGE(
        DATE(EXTRACT(YEAR FROM CURRENT_DATE) || '-' || 
             EXTRACT(MONTH FROM birthdate) || '-' || 
             EXTRACT(DAY FROM birthdate)),
        birthdate
    )) as turning_age,
    -- Calculate days until birthday
    CASE 
        WHEN DATE_PART('doy', birthdate) >= DATE_PART('doy', CURRENT_DATE)
        THEN DATE_PART('doy', birthdate) - DATE_PART('doy', CURRENT_DATE)
        ELSE 365 - DATE_PART('doy', CURRENT_DATE) + DATE_PART('doy', birthdate)
    END as days_until
FROM planning_center.people_people
WHERE birthdate IS NOT NULL
  AND status = 'active'
  AND (
    -- Birthday in current year
    (EXTRACT(MONTH FROM birthdate) = EXTRACT(MONTH FROM CURRENT_DATE) 
     AND EXTRACT(DAY FROM birthdate) >= EXTRACT(DAY FROM CURRENT_DATE))
    OR
    (EXTRACT(MONTH FROM birthdate) = EXTRACT(MONTH FROM CURRENT_DATE + INTERVAL '1 month'))
  )
ORDER BY 
    EXTRACT(MONTH FROM birthdate),
    EXTRACT(DAY FROM birthdate);
```

## Working with Households

### List All Households

```sql theme={null}
-- View all households with member counts
SELECT 
    household_id,
    name,
    member_count,
    primary_contact_name,
    created_at
FROM planning_center.people_households
ORDER BY name
LIMIT 100;
```

### Find Household Members

```sql theme={null}
-- Get all members of households
SELECT 
    h.household_id,
    h.name as household_name,
    h.member_count,
    p.name as member_name,
    p.birthdate,
    CASE 
        WHEN p.child = true THEN 'Child'
        ELSE 'Adult'
    END as member_type,
    hm.pending
FROM planning_center.people_households h
JOIN planning_center.people_household_memberships hm 
    ON h.household_id = hm.household_id
JOIN planning_center.people_people p 
    ON hm.person_id = p.person_id
WHERE p.status = 'active'
  AND hm.pending = false
ORDER BY h.name, p.birthdate;
```

### Large Families

```sql theme={null}
-- Find households with 5 or more members
SELECT 
    household_id,
    name,
    member_count,
    primary_contact_name
FROM planning_center.people_households
WHERE member_count >= 5
ORDER BY member_count DESC;
```

### Single-Person Households

```sql theme={null}
-- Identify people living alone
SELECT 
    h.household_id,
    h.name as household_name,
    p.name as person_name,
    p.birthdate,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate)) as age
FROM planning_center.people_households h
JOIN planning_center.people_household_memberships hm 
    ON h.household_id = hm.household_id
JOIN planning_center.people_people p 
    ON hm.person_id = p.person_id
WHERE h.member_count = 1
  AND p.status = 'active'
ORDER BY p.birthdate;
```

## Contact Information

### People with Email Addresses

```sql theme={null}
-- Find primary email addresses
SELECT 
    p.person_id,
    p.name,
    e.address as email,
    e.location as email_type,
    e.is_primary,
    e.blocked
FROM planning_center.people_people p
JOIN planning_center.people_email_relationships er 
    ON p.person_id = er.person_id
JOIN planning_center.people_emails e 
    ON er.email_id = e.email_id
WHERE p.status = 'active'
  AND e.is_primary = true
  AND e.blocked = false
ORDER BY p.last_name, p.first_name
LIMIT 100;
```

### People with Phone Numbers

```sql theme={null}
-- Find phone numbers by type
SELECT 
    p.person_id,
    p.name,
    pn.number,
    pn.location as phone_type,
    pn.is_primary,
    pn.carrier_name
FROM planning_center.people_people p
JOIN planning_center.people_phone_number_relationships pnr 
    ON p.person_id = pnr.person_id
JOIN planning_center.people_phone_numbers pn 
    ON pnr.phone_number_id = pn.phone_number_id
WHERE p.status = 'active'
  AND pn.is_primary = true
ORDER BY p.last_name, p.first_name
LIMIT 100;
```

### Complete Contact Info

```sql theme={null}
-- Get all contact methods for active people
SELECT 
    p.person_id,
    p.name,
    e.address as email,
    pn.number as phone,
    a.street,
    a.city,
    a.state,
    a.zip
FROM planning_center.people_people p
LEFT JOIN planning_center.people_email_relationships er 
    ON p.person_id = er.person_id
LEFT JOIN planning_center.people_emails e 
    ON er.email_id = e.email_id AND e.is_primary = true
LEFT JOIN planning_center.people_phone_number_relationships pnr 
    ON p.person_id = pnr.person_id
LEFT JOIN planning_center.people_phone_numbers pn 
    ON pnr.phone_number_id = pn.phone_number_id AND pn.is_primary = true
LEFT JOIN planning_center.people_addresses a 
    ON p.person_id = a.person_id AND a.is_primary = true
WHERE p.status = 'active'
ORDER BY p.last_name, p.first_name
LIMIT 50;
```

### People Missing Contact Info

```sql theme={null}
-- Find people without email or phone
SELECT 
    p.person_id,
    p.name,
    p.created_at,
    CASE 
        WHEN e.email_id IS NULL AND pn.phone_number_id IS NULL THEN 'No Contact Info'
        WHEN e.email_id IS NULL THEN 'No Email'
        WHEN pn.phone_number_id IS NULL THEN 'No Phone'
        ELSE 'Has Contact Info'
    END as contact_status
FROM planning_center.people_people p
LEFT JOIN planning_center.people_email_relationships er 
    ON p.person_id = er.person_id
LEFT JOIN planning_center.people_emails e 
    ON er.email_id = e.email_id
LEFT JOIN planning_center.people_phone_number_relationships pnr 
    ON p.person_id = pnr.person_id
LEFT JOIN planning_center.people_phone_numbers pn 
    ON pnr.phone_number_id = pn.phone_number_id
WHERE p.status = 'active'
  AND p.child = false  -- Adults only
  AND (e.email_id IS NULL OR pn.phone_number_id IS NULL)
ORDER BY p.created_at DESC;
```

## Membership and Status

### Membership Levels

```sql theme={null}
-- Count people by membership level
SELECT 
    membership,
    COUNT(*) as count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) as percentage
FROM planning_center.people_people
WHERE status = 'active'
GROUP BY membership
ORDER BY count DESC;
```

### New Members

```sql theme={null}
-- People who became members recently
SELECT 
    person_id,
    name,
    membership,
    created_at,
    updated_at
FROM planning_center.people_people
WHERE status = 'active'
  AND membership = 'Member'
  AND updated_at >= CURRENT_DATE - INTERVAL '90 days'
ORDER BY updated_at DESC;
```

### Permission Levels

```sql theme={null}
-- Find people with administrative permissions
SELECT 
    person_id,
    name,
    site_administrator,
    people_permissions,
    can_create_forms,
    can_email_lists
FROM planning_center.people_people
WHERE status = 'active'
  AND (
    site_administrator = true
    OR people_permissions IS NOT NULL
    OR can_create_forms = true
    OR can_email_lists = true
  )
ORDER BY name;
```

### Inactive People

```sql theme={null}
-- Recently inactivated people (for follow-up)
SELECT 
    p.person_id,
    p.name,
    p.inactivated_at,
    p.membership,
    ir.name as inactive_reason
FROM planning_center.people_people p
LEFT JOIN planning_center.people_inactive_reasons ir 
    ON p.inactive_reason_id = ir.inactive_reason_id
WHERE p.status = 'inactive'
  AND p.inactivated_at >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY p.inactivated_at DESC;
```

## Date-Based Queries

### Anniversary This Month

```sql theme={null}
-- Wedding anniversaries this month
SELECT 
    person_id,
    name,
    anniversary,
    TO_CHAR(anniversary, 'Month DD') as anniversary_date,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, anniversary)) as years_married
FROM planning_center.people_people
WHERE anniversary IS NOT NULL
  AND status = 'active'
  AND EXTRACT(MONTH FROM anniversary) = EXTRACT(MONTH FROM CURRENT_DATE)
ORDER BY EXTRACT(DAY FROM anniversary);
```

### People Added by Month

```sql theme={null}
-- Track growth over the last year
SELECT 
    TO_CHAR(created_at, 'YYYY-MM') as month,
    COUNT(*) as people_added,
    COUNT(*) FILTER (WHERE child = true) as children_added,
    COUNT(*) FILTER (WHERE child = false OR child IS NULL) as adults_added
FROM planning_center.people_people
WHERE created_at >= CURRENT_DATE - INTERVAL '12 months'
  AND status = 'active'
GROUP BY TO_CHAR(created_at, 'YYYY-MM')
ORDER BY month DESC;
```

### Long-Time Members

```sql theme={null}
-- People who have been in database 5+ years
SELECT 
    person_id,
    name,
    membership,
    created_at,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, created_at)) as years_in_database
FROM planning_center.people_people
WHERE status = 'active'
  AND created_at <= CURRENT_DATE - INTERVAL '5 years'
ORDER BY created_at
LIMIT 100;
```

## Basic Statistics

### Overall Demographics Summary

```sql theme={null}
-- High-level congregation overview
SELECT 
    COUNT(*) as total_people,
    COUNT(*) FILTER (WHERE status = 'active') as active_people,
    COUNT(*) FILTER (WHERE membership = 'Member') as members,
    COUNT(*) FILTER (WHERE child = true) as children,
    COUNT(*) FILTER (WHERE graduation_year IS NOT NULL) as students,
    COUNT(*) FILTER (WHERE gender = 'Male') as males,
    COUNT(*) FILTER (WHERE gender = 'Female') as females,
    ROUND(AVG(EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate))) FILTER (WHERE birthdate IS NOT NULL), 1) as avg_age
FROM planning_center.people_people;
```

### Age Group Distribution

```sql theme={null}
-- Breakdown by age categories
WITH age_groups AS (
    SELECT 
        CASE 
            WHEN birthdate IS NULL THEN 'Unknown'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) < 1 THEN 'Infant'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) < 5 THEN 'Preschool'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) < 12 THEN 'Elementary'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) < 18 THEN 'Youth'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) < 30 THEN 'Young Adult'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) < 50 THEN 'Adult'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) < 65 THEN 'Middle Age'
            ELSE 'Senior'
        END as age_group
    FROM planning_center.people_people
    WHERE status = 'active'
)
SELECT 
    age_group,
    COUNT(*) as count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) as percentage
FROM age_groups
GROUP BY age_group
ORDER BY 
    CASE age_group
        WHEN 'Infant' THEN 1
        WHEN 'Preschool' THEN 2
        WHEN 'Elementary' THEN 3
        WHEN 'Youth' THEN 4
        WHEN 'Young Adult' THEN 5
        WHEN 'Adult' THEN 6
        WHEN 'Middle Age' THEN 7
        WHEN 'Senior' THEN 8
        ELSE 9
    END;
```

### Household Statistics

```sql theme={null}
-- Household composition analysis
SELECT 
    'Total Households' as metric,
    COUNT(*)::text as value
FROM planning_center.people_households
UNION ALL
SELECT 
    'Average Household Size',
    ROUND(AVG(member_count), 2)::text
FROM planning_center.people_households
UNION ALL
SELECT 
    'Single-Person Households',
    COUNT(*)::text
FROM planning_center.people_households
WHERE member_count = 1
UNION ALL
SELECT 
    'Families with Children',
    COUNT(DISTINCT h.household_id)::text
FROM planning_center.people_households h
JOIN planning_center.people_household_memberships hm ON h.household_id = hm.household_id
JOIN planning_center.people_people p ON hm.person_id = p.person_id
WHERE p.child = true
ORDER BY metric;
```

## Tips for Writing Queries

### 1. Always Filter by Status

```sql theme={null}
WHERE status = 'active'  -- Unless you specifically need inactive people
```

### 2. Handle NULL Values

```sql theme={null}
-- Use COALESCE to provide defaults
COALESCE(nickname, first_name) as preferred_name

-- Check for NULL before calculations
WHERE birthdate IS NOT NULL
```

### 3. Case-Insensitive Searches

```sql theme={null}
WHERE LOWER(name) LIKE '%smith%'  -- Finds Smith, SMITH, smith, etc.
```

### 4. Date Calculations

```sql theme={null}
-- Age from birthdate
EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) as age

-- Days since date
CURRENT_DATE - created_at::date as days_since
```

### 5. Use Proper Joins

```sql theme={null}
-- LEFT JOIN when data might not exist
LEFT JOIN planning_center.people_emails e ON ...

-- INNER JOIN when data must exist
JOIN planning_center.people_household_memberships hm ON ...
```

## Common Issues & Solutions

### Issue: Duplicate people in results

**Solution**: Check your joins and consider using DISTINCT.

### Issue: Missing people who should appear

**Solution**: Verify status = 'active' and check relationship joins.

### Issue: Age calculations returning NULL

**Solution**: Ensure birthdate IS NOT NULL before calculating.

### Issue: Contact info not showing

**Solution**: Use LEFT JOIN for optional data like emails and phones.

## Next Steps

Ready for more complex queries? Continue with:

* [Data Model](/planning-center/people/data-model) - Master joining People tables
* [Advanced Queries](/planning-center/people/advanced-queries) - Complex analysis and reporting
* [Reporting Examples](/planning-center/people/reporting-examples) - Real-world ministry applications