# SQL style guide

## Core philosophy
From PEP8: 
- Consistency with this style guide is important
- Consistency within a project is more important
- Consistency within one module or function is the most important
- However, know when to be inconsistent -- sometimes style guide recommendations just aren't applicable

## Core rules
- **Use lowercase SQL keywords** (not uppercase)
- Use `snake_case` for all identifiers (no CamelCase)
- Names must begin with a letter and may not end in underscore
- Only use letters, numbers, and underscores in names
- Be explicit: always use `AS` for aliases, specify JOIN types
- Root keywords on their own line (except with single argument)
- Multi-line arguments must be indented relative to root keyword
- Use **ISO 8601 date format**: `yyyy-mm-ddThh:mm:ss.sssss`
- Foreign key naming: `user_id` to reference `users` table (singular + _id)
- Use meaningful aliases that reflect the data (not just single letters)

## Formatting

### Keywords and alignment
```sql
-- Root keywords left-aligned
-- Arguments indented relative to root keyword
select
  client_id,
  submission_date
from main_summary
where
  sample_id = '42'
  and submission_date > '20180101'
limit 10;
```

### Comments
```sql
/* Block comments for multi-line descriptions */
-- Line comments for single line notes
select
  client_id,  -- user identifier
  submission_date
from main_summary;
```

### Parentheses
```sql
-- Opening paren ends the line
-- Closing paren aligns with starting line
-- Contents indented
with sample as (
  select
    client_id,
    submission_date
  from main_summary
  where sample_id = '42'
)
```

### Boolean operators
```sql
-- AND/OR at beginning of line
where
  submission_date > '20180101'
  and sample_id = '42'
```

## Table design rules
- Always add `id` column of type `identity generated always`
- Always add table comments using `comment on table...`
- Default to `public` schema
- Include schema in queries for clarity
- Use singular table names with `_id` suffix for foreign keys

## Best practices
- Use CTEs instead of nested queries
- Explicit column names in GROUP BY (except for expressions - see below)
- Functions treated as identifiers: `date_trunc()` not `DATE_TRUNC()`
- One argument per line for multi-argument clauses
- Use meaningful aliases that reflect the data being selected

### GROUP BY exception
```sql
-- Acceptable: use numbers to avoid repeating complex expressions
select
  date_trunc('minute', xact_start) as xact_start_minute,
  count(*)
from pg_stat_activity
group by 1
order by 1;
```

## Examples

### Good
```sql
select
  t.client_id as client_id,
  date(t.created_at) as day
from telemetry as t
inner join users as u
  on t.user_id = u.id
where
  t.submission_date > '2019-07-01'
  and t.sample_id = '10'
group by t.client_id, day;
```

### Bad
```sql
SELECT t.client_id, DATE(t.created_at) day
FROM telemetry t, users u
WHERE t.user_id = u.id AND t.submission_date > '2019-07-01'
GROUP BY 1, 2;
```