# View: assigned_reviewer_change_log

**View Name:** assigned_reviewer_change_log
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations_Log.Vendor_Log, Orchard_App_Reporting_V2, Orchard_App_Reporting_V2.Art_Relations_Prod_Art_Relations_Log, Previous_States, Raw_Logs + 1 more`
**File Path:** `views/periscope_migration/assigned_reviewer_change_log.view.lkml`

## Overview

- **File Size:** 6097 bytes
- **Lines of Code:** 206
- **Dimensions:** 10
- **Measures:** 0
- **Dimension Groups:** 2
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `updated_by` | string |
| `previous_assigned_reviewer` | string |
| `updated_assigned_reviewer` | string |
| `account_id` | string |
| `account_name` | string |
| `account_service_tier` | string |
| `relationship_manager` | string |
| `rm_territory` | string |
| `account_country` | string |
| `account_owner` | string |

## Dimension Groups

| Name | Type |
|------|------|
| `date_of_change` | time |
| `account_signed_date` | time |

## SQL Comments

- 1. Condense history down to just the distinct timestamps
- 2. Grab the reviewer from the strictly older timestamp
- 3. Run the pattern match with the new custom sort logic integrated
- Join our "previous state" helper column back to the raw logs
- TIE BREAKER: If tied, put the row that matches the previous state first (0)

## Derived Table

```sql
sql:
    WITH raw_logs AS (
    SELECT
        vl.log_timestamp,
        vl.vendor_id,
        vl.last_modified_by,
        vl.assigned_reviewer
    FROM ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS_LOG.VENDOR_LOG vl
    WHERE vl._fivetran_deleted = FALSE
      AND vl.log_timestamp > '2023-07-31 20:43:09 +0000'::TIMESTAMP_TZ
),

-- 1. Condense history down to just the distinct timestamps
timestamp_states AS (
    SELECT
        vendor_id,
        log_timestamp,
        MAX(assigned_reviewer) AS grouped_reviewer
    FROM raw_logs
    GROUP BY vendor_id, log_timestamp
),

-- 2. Grab the reviewer from the strictly older timestamp
previous_states AS (
    SELECT
        vendor_id,
        log_timestamp,
        LAG(grouped_reviewer) OVER (PARTITION BY vendor_id ORDER BY log_timestamp ASC) AS prev_reviewer
    FROM timestamp_states
),

-- 3. Run the pattern match with the new custom sort logic integrated
cr_assigned_reviewer_changelog AS (
    SELECT
        mr.*,
        ou.f_name,
        ou.l_name,
        lm.territory
    FROM (
        -- Join our "previous state" helper column back to the raw logs
        SELECT
            r.log_timestamp,
            r.vendor_id,
            r.last_modified_by,
            r.assigned_reviewer,
            p.prev_reviewer
        FROM raw_logs r
        LEFT JOIN previous_states p
            ON r.vendor_id = p.vendor_id
            AND r.log_timestamp = p.log_timestamp
    ) x
    MATCH_RECOGNIZE(
        PARTITION BY x.vendor_id
        ORDER BY
            x.log_timestamp ASC,
            -- TIE BREAKER: If tied, put the row that matches the previous state first (0)
            CASE WHEN x.assigned_reviewer = x.prev_reviewer THEN 0 ELSE 1 END ASC
        MEASURES
            FIRST(log_timestamp) AS previous_log_timestamp,
            LAST(log_timestamp) AS updated_log_timestamp,
            LAST(last_modified_by) AS updated_by,
            FIRST(assigned_reviewer) AS previous_assigned_reviewer,
            LAST(assigned_reviewer) AS updated_assigned_reviewer
        ONE ROW PER MATCH
        AFTER MATCH SKIP TO NEXT ROW
        PATTERN (source updated_source{1
```