> ## Documentation Index
> Fetch the complete documentation index at: https://docs.winningvariant.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Assignments via SQL

> How to get or set experiment assignments with SQL.

## Overview

Assignment can be set or retrieved directly in SQL. This is useful when doing analysis (querying directly from `experimentation.assignments` or if wanting to implement an experiment directly in SQL.

## Roles

A user must be assigned of the following [application roles](/snowflake-app-details#application-roles) to execute any of the examples described on this page:

* `admin`
* `editor`
* `scientist`

## Get assignments

To get assignments for a given subject, perform a select against the `assignments` table:

```SQL theme={null}
SELECT variant_id
  FROM experimentation.assignments
  WHERE subject_id = '<subject_id>' AND experiment_id = '<experiment_id>';
```

## Get or make assignments

Gets an assignment or makes one if it doesn't exist:

```SQL theme={null}
SELECT experimentation.create_assignment('<subject_id>', '<experiment_id>');
```

Returns the new or existing variant assigned.

## Examples

### Manually get or create a single assignment

This example gets an assignment for user with ID `user_0001` in experiment `home-cta`.

```SQL theme={null}
SELECT experimentation.create_assignment('user_0001', 'home-cta');
```

### Get an assignment for each `user_id` value in a table

```SQL theme={null}
SELECT
  user_id,
  experimentation.create_assignment(user_id, '<experiment_id>') AS assignment
FROM
  my_user_table;
```

### Split test different Cortex models

This example demonstrates how you can run a SQL query that produces an output from Cortex's `COMPLETE` method, testing different model versions for various customers. Winning Variant is used to determine which model should be used per customer ID. Later, we would tie business KPIs (i.e., revenue, churn, returns, etc) back to each variant to see which model should be deployed.

We have the following declared for this example:

* A 50/50 test between control (`llama3.2-1b` model) and treatment (`claude-3-5-sonnet`)
* The Winning Variant experiment ID is `cortex-test`
* The ID of *your* user in this example is `abc123`

```sql theme={null}
-- Get an assignment from Winning Variant for the experiment
WITH assignment AS (
    SELECT create_assignment('abc123', 'cortex-test') AS variant
)

-- If the assigned variant for this user is 'TREATMENT',
-- use 'claude-3-5-sonnet', otherwise use 'llama3.2-1b' (CONTROL)
SELECT
  CASE
    WHEN variant = 'TREATMENT'
    THEN SNOWFLAKE.CORTEX.COMPLETE('claude-3-5-sonnet', 'Is a hot dog a sandwich?')
    ELSE SNOWFLAKE.CORTEX.COMPLETE('llama3.2-1b', 'Is a hot dog a sandwich?')
  END AS result
FROM assignment;
```