# Query Cookbook

Example queries and patterns for each major dataset in the `CLINICOGENOMICS` warehouse. Start here for a working query instead of a blank editor.

## LIMS foundation queries

The `LIMS_PUB` product base tables are the primary analytics source — wide clinical bases joined from staging, clustered on `casefile_id`. A typical starting point counts distinct casefiles in the Renasight base:

```sql
SELECT COUNT(DISTINCT casefile_id) AS casefiles
FROM CLINICOGENOMICS.LIMS_PUB.BASE_RENASIGHT;
```

Use the per-product base tables: `BASE_RENASIGHT` (Organ Health), and `BASE_SIGNATERA` / `BASE_ALTERA` / `BASE_LATITUDE` (Oncology). See the **Schema Reference** for the full list.

> **Don't use BASE_OH / BASE_ONC**
>
> The legacy `BASE_OH` and `BASE_ONC` wide tables are being retired. Point new queries at the product base tables instead.

## Cohort & partner queries

Partner cohort tables in `LIMS_COHORTS` are pre-filtered and pre-joined — query them directly rather than re-joining to foundation. List what is available, then query a cohort:

```sql
SHOW TABLES IN SCHEMA CLINICOGENOMICS.LIMS_COHORTS;

SELECT COUNT(DISTINCT casefile_id) AS patients
FROM CLINICOGENOMICS.LIMS_COHORTS.OH_VERTEX_SPONSORED;
```

> **Cohorts already include their joins**
>
> Cohort tables include all relevant joins. Re-joining a cohort back to the foundation tables is an anti-pattern — query the cohort directly.

## More cookbooks

Dataset-specific cookbooks live on their own pages:

- **Genomics (VARIANTS) Queries** — performance-critical variant-call patterns built around Search Optimization columns.
- **Dashboard & Reporting Queries** — the `DASHBOARD` marts and ADS clinical abstraction data.
- **Cortex Intelligence Agent** — natural-language SQL via the AI agent.