dc-query-building

What this skill does

# Query Building Skill

This skill helps you construct CubeQuery objects for querying Drizzle Cube's semantic layer.

## CubeQuery Structure

```typescript
interface CubeQuery {
  measures?: string[]                    // Aggregations
  dimensions?: string[]                  // Groupings
  timeDimensions?: TimeDimension[]       // Time-based analysis
  filters?: Filter[]                     // Data filtering
  order?: Record<string, 'asc' | 'desc'> // Sorting
  limit?: number                         // Row limit
  offset?: number                        // Pagination offset
  fillMissingDatesValue?: number | null  // Fill gaps in time series
}
```

## Field Naming Convention

All fields use `CubeName.fieldName` format:

```typescript
// Measures
'Sales.totalRevenue'
'Orders.count'
'Employees.avgSalary'

// Dimensions
'Products.category'
'Customers.region'
'Employees.department'

// Time Dimensions
'Orders.createdAt'
'Events.timestamp'
```

## Basic Query Examples

### Count Query
```typescript
{
  measures: ['Employees.count']
}
```

### Count with Dimension
```typescript
{
  measures: ['Employees.count'],
  dimensions: ['Employees.department']
}
```

### Multiple Measures
```typescript
{
  measures: [
    'Sales.totalRevenue',
    'Sales.avgOrderValue',
    'Orders.count'
  ],
  dimensions: ['Products.category']
}
```

### With Ordering and Limit
```typescript
{
  measures: ['Sales.totalRevenue'],
  dimensions: ['Customers.name'],
  order: {
    'Sales.totalRevenue': 'desc'
  },
  limit: 10
}
```

## Time Dimensions

**IMPORTANT:** `timeDimensions` groups results BY time (one row per period). If you only want to filter by a date range WITHOUT grouping by time, use `filters` with `inDateRange` instead. See [Time Filtering vs Time Grouping](#time-filtering-vs-time-grouping) below.

### Basic Time Query
```typescript
{
  measures: ['Sales.totalRevenue'],
  timeDimensions: [{
    dimension: 'Orders.createdAt',
    granularity: 'month'
  }]
}
```

### With Date Range
```typescript
{
  measures: ['Sales.totalRevenue'],
  timeDimensions: [{
    dimension: 'Orders.createdAt',
    granularity: 'day',
    dateRange: ['2024-01-01', '2024-03-31']
  }]
}
```

### Predefined Date Ranges
```typescript
// Last 7 days
dateRange: 'last 7 days'

// This month
dateRange: 'this month'

// Last quarter
dateRange: 'last quarter'

// Year to date
dateRange: 'from 1 year ago to now'
```

### Granularity Options

| Granularity | Description |
|-------------|-------------|
| `hour` | Hourly aggregation |
| `day` | Daily aggregation |
| `week` | Weekly aggregation |
| `month` | Monthly aggregation |
| `quarter` | Quarterly aggregation |
| `year` | Yearly aggregation |

### Fill Missing Dates
```typescript
{
  measures: ['Orders.count'],
  timeDimensions: [{
    dimension: 'Orders.createdAt',
    granularity: 'day',
    dateRange: ['2024-01-01', '2024-01-31'],
    fillMissingDates: true
  }],
  fillMissingDatesValue: 0  // Fill gaps with 0
}
```

### Period Comparison
```typescript
{
  measures: ['Sales.totalRevenue'],
  timeDimensions: [{
    dimension: 'Sales.createdAt',
    granularity: 'month',
    dateRange: ['2024-01-01', '2024-03-31'],
    compareDateRange: [
      ['2023-01-01', '2023-03-31']  // Compare to previous year
    ]
  }]
}
```

### Time Filtering vs Time Grouping

**This is a common source of errors.** Choose the right approach:

| Goal | Method | Example |
|------|--------|---------|
| Break down by time periods | `timeDimensions` with `granularity` | "Revenue **by month**" |
| Aggregate within a time range | `filters` with `inDateRange` | "Total revenue **for last quarter**" |
| Top N over a time period | `filters` with `inDateRange` | "Top 5 employees **over past 3 months**" |

**Using timeDimensions (groups by time):**
```typescript
// Returns ONE ROW PER MONTH
{
  measures: ['Sales.totalRevenue'],
  timeDimensions: [{
    dimension: 'Sales.createdAt',
    granularity: 'month',
    dateRange: 'last quarter'
  }]
}
```

**Using filters (only constrains, no grouping):**
```typescript
// Returns SINGLE AGGREGATED ROW (or rows by other dimensions)
{
  measures: ['Sales.totalRevenue'],
  filters: [
    { member: 'Sales.createdAt', operator: 'inDateRange', values: ['last quarter'] }
  ]
}
```

**Predefined date ranges for filters:** `'last 7 days'`, `'last month'`, `'last 3 months'`, `'last quarter'`, `'this year'`, `'from 1 year ago to now'`

## Filters

### Filter Structure
```typescript
interface Filter {
  member: string              // Field name
  operator: FilterOperator    // Comparison operator
  values: any[]               // Values to compare
  dateRange?: string | [string, string]  // For date operators
}
```

### Filter Operators

**Equality:**
```typescript
{ member: 'Products.status', operator: 'equals', values: ['active'] }
{ member: 'Products.status', operator: 'notEquals', values: ['deleted'] }
```

**String Matching:**
```typescript
{ member: 'Products.name', operator: 'contains', values: ['Premium'] }
{ member: 'Products.name', operator: 'notContains', values: ['Test'] }
{ member: 'Products.name', operator: 'startsWith', values: ['Pro'] }
{ member: 'Products.name', operator: 'endsWith', values: ['Edition'] }
```

**Numeric Comparison:**
```typescript
{ member: 'Orders.amount', operator: 'gt', values: [100] }
{ member: 'Orders.amount', operator: 'gte', values: [100] }
{ member: 'Orders.amount', operator: 'lt', values: [1000] }
{ member: 'Orders.amount', operator: 'lte', values: [1000] }
{ member: 'Orders.amount', operator: 'between', values: [100, 500] }
```

**Array Membership:**
```typescript
{ member: 'Products.category', operator: 'in', values: ['Electronics', 'Clothing'] }
{ member: 'Products.category', operator: 'notIn', values: ['Archived', 'Draft'] }
```

**Null Checks:**
```typescript
{ member: 'Customers.email', operator: 'set', values: [] }      // NOT NULL
{ member: 'Customers.email', operator: 'notSet', values: [] }   // IS NULL
```

**Date Operators:**
```typescript
{ member: 'Orders.createdAt', operator: 'inDateRange', values: ['2024-01-01', '2024-12-31'] }
{ member: 'Orders.createdAt', operator: 'beforeDate', values: ['2024-01-01'] }
{ member: 'Orders.createdAt', operator: 'afterDate', values: ['2024-06-01'] }
```

### Grouped Filters (AND/OR)

**AND Logic:**
```typescript
filters: [{
  type: 'and',
  filters: [
    { member: 'Products.isActive', operator: 'equals', values: [true] },
    { member: 'Products.stock', operator: 'gt', values: [0] }
  ]
}]
```

**OR Logic:**
```typescript
filters: [{
  type: 'or',
  filters: [
    { member: 'Orders.status', operator: 'equals', values: ['pending'] },
    { member: 'Orders.status', operator: 'equals', values: ['processing'] }
  ]
}]
```

**Complex Nested Logic:**
```typescript
// (isActive = true) AND (category = 'A' OR category = 'B')
filters: [{
  type: 'and',
  filters: [
    { member: 'Products.isActive', operator: 'equals', values: [true] },
    {
      type: 'or',
      filters: [
        { member: 'Products.category', operator: 'equals', values: ['A'] },
        { member: 'Products.category', operator: 'equals', values: ['B'] }
      ]
    }
  ]
}]
```

## MultiQueryConfig

Combine multiple queries with merge strategies:

```typescript
interface MultiQueryConfig {
  queries: CubeQuery[]
  mergeStrategy: 'concat' | 'merge'
  mergeKeys?: string[]       // For 'merge' strategy
  queryLabels?: string[]     // Optional labels
}
```

### Concat Strategy
Appends results with `__queryIndex` marker:

```typescript
{
  queries: [
    { measures: ['Sales.revenue'], dimensions: ['Products.category'] },
    { measures: ['Returns.total'], dimensions: ['Products.category'] }
  ],
  mergeStrategy: 'concat',
  queryLabels: ['Sales', 'Returns']
}
```

### Merge Strategy
Aligns results by common dimension:

```typescript
{
  queries: [
    { measures: ['Sales.revenue'], dimensions: ['Products.category'] },
    { measures: ['Returns.total'], dimensions: ['Products.category'] }
  ],
  mergeStrat