# SQL Query Patterns for IDC

**Tested with:** idc-index 0.12.5 (IDC data version v24)

Quick reference for common SQL query patterns when working with IDC data. For detailed examples with context, see the "Core Capabilities" section in the main SKILL.md.

## When to Use This Guide

Load this guide when you need quick-reference SQL patterns for:
- Discovering available filter values (modalities, body parts, manufacturers)
- Finding annotations and segmentations across collections
- Querying slide microscopy and annotation data
- Estimating download sizes before download
- Linking imaging data to clinical data
- Filtering by 3D volume geometry validity (volume_geometry_index)
- Finding RT Structure Set series and ROI metadata (rtstruct_index)
- Filtering by CT/MR/PET acquisition parameters (ct_index, mr_index, pt_index)

For table schemas, DataFrame access, and join column references, see `references/index_tables_guide.md`.

## Prerequisites

Needs `idc-index` installed — run `python scripts/check_version.py`, which reports the installed
version and prints the install command for the interpreter you are running.

```python
from idc_index import IDCClient
client = IDCClient()
```

## Overall Data Scale

Counts and total size across all of IDC — useful for orienting a user, and for sanity-checking
that the index loaded the release you expect:

```python
stats = client.sql_query("""
    SELECT
        COUNT(DISTINCT collection_id) as collections,
        COUNT(DISTINCT analysis_result_id) as analysis_results,
        COUNT(DISTINCT PatientID) as patients,
        COUNT(DISTINCT StudyInstanceUID) as studies,
        COUNT(DISTINCT SeriesInstanceUID) as series,
        SUM(instanceCount) as instances,
        SUM(series_size_MB)/1000000 as size_TB
    FROM index
""")
print(stats)
```

### Per-collection breakdown

```python
# Get summary statistics from primary index
collections_summary = client.sql_query("""
    SELECT collection_id,
           COUNT(DISTINCT PatientID) as patients,
           COUNT(DISTINCT SeriesInstanceUID) as series,
           SUM(series_size_MB) as size_mb
    FROM index
    GROUP BY collection_id
    ORDER BY patients DESC
""")
```

For richer per-collection metadata — cancer types, tumor locations, species, supporting data —
query `collections_index` instead; for derived datasets, `analysis_results_index`. Both need
`client.fetch_index(...)` first:

```python
client.fetch_index("collections_index")
collections_info = client.sql_query("""
    SELECT collection_id, cancer_types, tumor_locations, species, subjects, supporting_data
    FROM collections_index
""")

client.fetch_index("analysis_results_index")
analysis_info = client.sql_query("""
    SELECT analysis_result_id, analysis_result_title, subjects, collections, modalities
    FROM analysis_results_index
""")
```

## Discover Available Filter Values

```python
# What modalities exist?
client.sql_query("SELECT DISTINCT Modality FROM index")

# What body parts for a specific modality?
client.sql_query("""
    SELECT DISTINCT BodyPartExamined, COUNT(*) as n
    FROM index WHERE Modality = 'CT' AND BodyPartExamined IS NOT NULL
    GROUP BY BodyPartExamined ORDER BY n DESC
""")

# What manufacturers for MR?
client.sql_query("""
    SELECT DISTINCT Manufacturer, COUNT(*) as n
    FROM index WHERE Modality = 'MR'
    GROUP BY Manufacturer ORDER BY n DESC
""")
```

## Find Annotations and Segmentations

**Note:** Not all image-derived objects belong to analysis result collections. Some annotations are deposited alongside original images. Use DICOM Modality or SOPClassUID to find all derived objects regardless of collection type.

```python
# Find ALL segmentations and structure sets by DICOM Modality
# SEG = DICOM Segmentation, RTSTRUCT = Radiotherapy Structure Set
client.sql_query("""
    SELECT collection_id, Modality, COUNT(*) as series_count
    FROM index
    WHERE Modality IN ('SEG', 'RTSTRUCT')
    GROUP BY collection_id, Modality
    ORDER BY series_count DESC
""")

# Find segmentations for a specific collection (includes non-analysis-result items)
client.sql_query("""
    SELECT SeriesInstanceUID, SeriesDescription, analysis_result_id
    FROM index
    WHERE collection_id = 'tcga_luad' AND Modality = 'SEG'
""")

# List analysis result collections (curated derived datasets)
client.fetch_index("analysis_results_index")
client.sql_query("""
    SELECT analysis_result_id, analysis_result_title, collections, modalities
    FROM analysis_results_index
""")

# Find analysis results for a specific source collection
client.sql_query("""
    SELECT analysis_result_id, analysis_result_title
    FROM analysis_results_index
    WHERE Collections LIKE '%tcga_luad%'
""")

# Use seg_index for detailed DICOM Segmentation metadata
client.fetch_index("seg_index")

# Get segmentation statistics by algorithm
client.sql_query("""
    SELECT AlgorithmName, AlgorithmType, COUNT(*) as seg_count
    FROM seg_index
    WHERE AlgorithmName IS NOT NULL
    GROUP BY AlgorithmName, AlgorithmType
    ORDER BY seg_count DESC
    LIMIT 10
""")

# Find segmentations for specific source images (e.g., chest CT)
client.sql_query("""
    SELECT
        s.SeriesInstanceUID as seg_series,
        s.AlgorithmName,
        s.total_segments,
        s.segmented_SeriesInstanceUID as source_series
    FROM seg_index s
    JOIN index src ON s.segmented_SeriesInstanceUID = src.SeriesInstanceUID
    WHERE src.Modality = 'CT' AND src.BodyPartExamined = 'CHEST'
    LIMIT 10
""")

# Find TotalSegmentator results with source image context
client.sql_query("""
    SELECT
        seg_info.collection_id,
        COUNT(DISTINCT s.SeriesInstanceUID) as seg_count,
        SUM(s.total_segments) as total_segments
    FROM seg_index s
    JOIN index seg_info ON s.SeriesInstanceUID = seg_info.SeriesInstanceUID
    WHERE s.AlgorithmName LIKE '%TotalSegmentator%'
    GROUP BY seg_info.collection_id
    ORDER BY seg_count DESC
""")

# Use ann_index and ann_group_index for Microscopy Bulk Simple Annotations
# ann_group_index has AnnotationGroupLabel, GraphicType, NumberOfAnnotations, AlgorithmName
client.fetch_index("ann_index")
client.fetch_index("ann_group_index")
client.sql_query("""
    SELECT g.AnnotationGroupLabel, g.GraphicType, g.NumberOfAnnotations, i.collection_id
    FROM ann_group_index g
    JOIN ann_index a ON g.SeriesInstanceUID = a.SeriesInstanceUID
    JOIN index i ON a.SeriesInstanceUID = i.SeriesInstanceUID
    WHERE g.AlgorithmName IS NOT NULL
    LIMIT 10
""")
# See references/digital_pathology_guide.md for AnnotationGroupLabel filtering, SM+ANN joins, and more
```

## Query Slide Microscopy and Annotation Data

Use `sm_index` for slide microscopy metadata and `ann_index`/`ann_group_index` for annotations on slides (DICOM ANN objects). Filter annotation groups by `AnnotationGroupLabel` to find annotations by name.

```python
client.fetch_index("sm_index")
client.fetch_index("ann_index")
client.fetch_index("ann_group_index")

# Example: find annotation groups by label within a collection
client.sql_query("""
    SELECT g.AnnotationGroupLabel, g.GraphicType, g.NumberOfAnnotations
    FROM ann_group_index g
    JOIN index i ON g.SeriesInstanceUID = i.SeriesInstanceUID
    WHERE i.collection_id = 'your_collection_id'
      AND LOWER(g.AnnotationGroupLabel) LIKE '%keyword%'
""")
```

See `references/digital_pathology_guide.md` for SM queries, ANN filtering patterns, SM+ANN cross-references, and join examples.

## Estimate Download Size

```python
# Size for specific criteria
client.sql_query("""
    SELECT SUM(series_size_MB) as total_mb, COUNT(*) as series_count
    FROM index
    WHERE collection_id = 'nlst' AND Modality = 'CT'
""")
```

## Link to Clinical Data

```python
client.fetch_index("clinical_index")

# Find collections with clinical data and their tables
client.sql_query("""
    SELECT collection_id, table_name, COUNT(DISTINCT column_label) as columns
    FROM clinical_index
    GROUP BY collection_id, table_name
    ORDER BY collection_id
""")
```

See `references/clinical_data_guide.md` for complete patterns including value mapping and patient cohort selection.

## Version Tracking — "What's New in IDC vX?"

Use `series_init_idc_version` and `series_revised_idc_version` in the main `index` table. Do NOT
use `prior_versions_index` for this — it contains only removed series.

```python
VERSION = 24  # Replace with target version

# Series added for the first time in vVERSION
client.sql_query(f"""
    SELECT collection_id,
           COUNT(DISTINCT SeriesInstanceUID) as new_series,
           ROUND(SUM(series_size_MB)/1000, 2) as size_GB
    FROM index
    WHERE series_init_idc_version = {VERSION}
    GROUP BY collection_id
    ORDER BY new_series DESC
""")

# Series revised (updated content) in vVERSION but originally added earlier
client.sql_query(f"""
    SELECT collection_id,
           COUNT(DISTINCT SeriesInstanceUID) as revised_series
    FROM index
    WHERE series_revised_idc_version = {VERSION}
      AND series_init_idc_version < {VERSION}
    GROUP BY collection_id
    ORDER BY revised_series DESC
""")

# When was each collection first added to IDC?
client.fetch_index("version_metadata_index")
client.sql_query("""
    WITH first_versions AS (
        SELECT collection_id, MIN(series_init_idc_version) as first_version
        FROM index
        GROUP BY collection_id
    )
    SELECT f.collection_id, f.first_version, v.version_timestamp as first_release_date
    FROM first_versions f
    JOIN version_metadata_index v ON f.first_version = v.idc_version
    ORDER BY f.first_version DESC
""")
```

## Troubleshooting

**Issue:** Query returns error "table not found"
- **Cause:** Index not fetched before query
- **Solution:** Call `client.fetch_index("table_name")` before using tables other than the primary `index`

**Issue:** LIKE pattern not matching expected results
- **Cause:** Case sensitivity or whitespace
- **Solution:** Use `LOWER(column)` for case-insensitive matching, `TRIM()` for whitespace

**Issue:** JOIN returns fewer rows than expected
- **Cause:** NULL values in join columns or no matching records
- **Solution:** Use `LEFT JOIN` to include rows without matches, check for NULLs with `IS NOT NULL`

## Volume Geometry Validation

`volume_geometry_index` covers single-frame CT, MR, and PT series. Fetch it before querying.

```python
client.fetch_index("volume_geometry_index")

# Series that form a regularly-spaced 3D volume (no resampling needed)
client.sql_query("""
    SELECT i.collection_id, i.SeriesInstanceUID, i.BodyPartExamined,
           v.obliquity_degrees
    FROM index i
    JOIN volume_geometry_index v ON i.SeriesInstanceUID = v.SeriesInstanceUID
    WHERE i.Modality = 'CT'
      AND v.regularly_spaced_3d_volume = TRUE
    LIMIT 10
""")

# Fraction of 3D-valid CT per collection
client.sql_query("""
    SELECT i.collection_id,
           COUNT(*) as total_ct,
           SUM(CASE WHEN v.regularly_spaced_3d_volume THEN 1 ELSE 0 END) as valid_3d,
           ROUND(100.0 * SUM(CASE WHEN v.regularly_spaced_3d_volume THEN 1 ELSE 0 END) / COUNT(*), 1) as pct_valid
    FROM index i
    JOIN volume_geometry_index v ON i.SeriesInstanceUID = v.SeriesInstanceUID
    WHERE i.Modality = 'CT'
    GROUP BY i.collection_id
    ORDER BY total_ct DESC
    LIMIT 10
""")
```

Key columns: `regularly_spaced_3d_volume` (composite flag), `obliquity_degrees` (0 = pure axial/sagittal/coronal), plus individual boolean checks: `single_orientation`, `orthogonal_orientation`, `unique_slice_positions`, `consistent_pixel_spacing`, `consistent_image_dimensions`, `uniform_slice_spacing`.

## RT Structure Sets

`rtstruct_index` has one row per RTSTRUCT series. Array columns (`ROINames`, `ROIGenerationAlgorithms`, `RTROIInterpretedTypes`) are stored as strings.

```python
client.fetch_index("rtstruct_index")

# RTSTRUCT series with ROI counts and names
client.sql_query("""
    SELECT i.collection_id, i.SeriesInstanceUID,
           r.total_rois, r.ROINames, r.RTROIInterpretedTypes,
           r.referenced_SeriesInstanceUID
    FROM index i
    JOIN rtstruct_index r ON i.SeriesInstanceUID = r.SeriesInstanceUID
    LIMIT 10
""")

# Collections with the most RTSTRUCT series
client.sql_query("""
    SELECT i.collection_id,
           COUNT(*) as rtstruct_series,
           ROUND(AVG(r.total_rois), 1) as avg_rois
    FROM index i
    JOIN rtstruct_index r ON i.SeriesInstanceUID = r.SeriesInstanceUID
    GROUP BY i.collection_id
    ORDER BY rtstruct_series DESC
    LIMIT 10
""")

# Find source CT series for a given RTSTRUCT
client.sql_query("""
    SELECT r.SeriesInstanceUID as rtstruct_uid,
           r.total_rois, r.ROINames,
           src.SeriesInstanceUID as source_ct_uid,
           src.collection_id, src.BodyPartExamined
    FROM rtstruct_index r
    JOIN index src ON r.referenced_SeriesInstanceUID = src.SeriesInstanceUID
    LIMIT 10
""")
```

## Modality Acquisition Parameters

`ct_index`, `mr_index`, and `pt_index` (added in idc-index 0.12.3) expose acquisition and reconstruction parameters for CT, MR, and PET series. All join on `SeriesInstanceUID`. Dose-modulated CT acquisitions have `_min`/`_max` columns for tube current, exposure, and exposure time.

```python
client.fetch_index("ct_index")
client.fetch_index("mr_index")
client.fetch_index("pt_index")

# CT: thin-slice series (≤2mm) with standard reconstruction
client.sql_query("""
    SELECT i.collection_id, i.SeriesInstanceUID, i.BodyPartExamined,
           c.SliceThickness, c.ConvolutionKernel, c.KVP
    FROM index i
    JOIN ct_index c ON i.SeriesInstanceUID = c.SeriesInstanceUID
    WHERE c.SliceThickness <= 2.0
      AND c.ConvolutionKernel IS NOT NULL
    LIMIT 10
""")

# CT: dose-modulated acquisitions (tube current varies across slices)
client.sql_query("""
    SELECT i.collection_id, c.SeriesInstanceUID,
           c.XRayTubeCurrent_min, c.XRayTubeCurrent_max, c.SliceThickness
    FROM ct_index c
    JOIN index i ON c.SeriesInstanceUID = i.SeriesInstanceUID
    WHERE c.XRayTubeCurrent_min != c.XRayTubeCurrent_max
    LIMIT 10
""")

# MR: DWI series (have non-null DiffusionBValue) at 3T
client.sql_query("""
    SELECT i.collection_id, i.SeriesInstanceUID, i.SeriesDescription,
           m.MagneticFieldStrength, m.DiffusionBValue
    FROM index i
    JOIN mr_index m ON i.SeriesInstanceUID = m.SeriesInstanceUID
    WHERE m.DiffusionBValue IS NOT NULL
      AND m.MagneticFieldStrength >= 2.9
    LIMIT 10
""")

# MR: multi-echo series (EchoTime stored as array with multiple values)
client.sql_query("""
    SELECT i.collection_id, i.SeriesInstanceUID,
           m.EchoTime, m.EchoTrainLength, m.ScanningSequence
    FROM index i
    JOIN mr_index m ON i.SeriesInstanceUID = m.SeriesInstanceUID
    WHERE m.EchoTrainLength > 1
    LIMIT 10
""")

# PET: FDG studies with specific reconstruction method
client.sql_query("""
    SELECT i.collection_id, i.SeriesInstanceUID,
           p.RadionuclideCodeMeaning, p.ReconstructionMethod,
           p.Units, p.DecayCorrection
    FROM index i
    JOIN pt_index p ON i.SeriesInstanceUID = p.SeriesInstanceUID
    WHERE p.RadionuclideCodeMeaning LIKE '%fluorodeoxyglucose%'
    LIMIT 10
""")

# PET: dynamic acquisitions (ActualFrameDuration is array with multiple values)
client.sql_query("""
    SELECT i.collection_id, i.SeriesInstanceUID,
           p.NumberOfTimeSlices, p.ActualFrameDuration
    FROM index i
    JOIN pt_index p ON i.SeriesInstanceUID = p.SeriesInstanceUID
    WHERE p.NumberOfTimeSlices > 1
    LIMIT 10
""")
```

Key columns by table (use `client.indices_overview["ct_index"]["schema"]` for the full list):
- **ct_index**: `SliceThickness`, `KVP`, `ConvolutionKernel`, `SpiralPitchFactor`, `XRayTubeCurrent_min/max`, `Exposure_min/max`, `PixelSpacing_row_mm/col_mm`, `Rows`, `Columns`
- **mr_index**: `MagneticFieldStrength`, `ScanningSequence`, `SequenceVariant`, `MRAcquisitionType`, `EchoTime` (array), `RepetitionTime`, `FlipAngle`, `DiffusionBValue` (array), `NumberOfTemporalPositions`, `ReceiveCoilName`
- **pt_index**: `RadionuclideCodeMeaning`, `Radiopharmaceutical`, `RadionuclideTotalDose`, `ReconstructionMethod`, `DecayCorrection`, `AttenuationCorrectionMethod`, `ActualFrameDuration` (array), `NumberOfTimeSlices`

## Resources

- `references/index_tables_guide.md` for table schemas, DataFrame access, and join column references
- `references/clinical_data_guide.md` for clinical data patterns and value mapping
- `references/digital_pathology_guide.md` for pathology-specific queries
- `references/bigquery_guide.md` for advanced queries requiring full DICOM metadata
- `references/parquet_access_guide.md` for direct Parquet queries without installing idc-index