# View: dt_mobile_app_opened

**View Name:** dt_mobile_app_opened
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Country, Art_Relations_Prod_Art_Relations.Orchadmin_Users, Art_Relations_Prod_Art_Relations.Subaccount, Art_Relations_Prod_Art_Relations.Vend_Contact, Art_Relations_Prod_Art_Relations.Vendor + 3 more`
**File Path:** `dt_mobile_app_opened.view.lkml`

## Overview

- **File Size:** 4961 bytes
- **Lines of Code:** 176
- **Dimensions:** 14
- **Measures:** 4
- **Dimension Groups:** 1
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `primary_key` | number |
| `operating_system` | string |
| `label_id` | number |
| `label_name` | string |
| `label_country` | string |
| `label_manager` | string |
| `subaccount_id` | number |
| `subaccount_name` | string |
| `user_id` | number |
| `user_name` | string |
| `account_type` | string |
| `account_id` | number |
| `account_name` | string |
| `first_open` | yesno |

## Measures

| Name | Type |
|------|------|
| `count_labels` | count_distinct |
| `count_users` | count_distinct |
| `count_opens` | count_distinct |
| `count_first_open` | count |

## Dimension Groups

| Name | Type |
|------|------|
| `timestamp` | time |

## Derived Table

```sql
sql:
WITH accounts AS (
    SELECT 0 AS account_type, vendor_id AS account_id, vendor_id, NULL AS subaccount_id
    FROM orchard_app_reporting_v2.art_relations_prod_art_relations.vendor
    UNION ALL
    SELECT 1, subaccount_id, vendor_id, subaccount_id
    FROM orchard_app_reporting_v2.art_relations_prod_art_relations.subaccount
),
application_opened AS (
    SELECT
        timestamp,
        'Apple iOS' AS operating_system,
        IFF(context_traits_account_type = 'vendor', 0, 1) AS account_type,
        context_traits_account_id AS account_id,
        context_traits_user_id
    FROM segment_events.mobile_orchard_prod.application_opened
    WHERE context_traits_user_id LIKE 'alw:%' AND TO_DATE(timestamp) >= '2019-03-01'
    UNION ALL
    SELECT
        timestamp,
        'Android',
        IFF(context_traits_account_type = 'vendor', 0, 1),
        context_traits_account_id,
        context_traits_user_id
    FROM segment_events.mobile_orchard_prod_android.application_opened
    WHERE context_traits_user_id LIKE 'alw:%' AND TO_DATE(timestamp) >= '2019-03-01'
),
base AS (
    SELECT ao.*,
        ac.vendor_id AS label_id,
        ac.subaccount_id,
        TRIM(ao.context_traits_user_id, 'alw:') AS user_id
    FROM application_opened ao
        LEFT JOIN accounts ac ON ao.account_type = ac.account_type AND ao.account_id = ac.account_id
),
first_open AS (
    SELECT user_id, MIN(timestamp) AS first_open
    FROM base
    GROUP BY 1
)
SELECT
    RANK() OVER ( ORDER BY timestamp ASC ) AS primary_key,
    b.timestamp,
    b.operating_system,
    b.label_id,
    IFF(v.company IS NULL OR v.company = '',v.name,v.company) AS label_name,
    c.name AS label_country,
    oa.f_name||' '||oa.l_name AS label_manager,
    b.subaccount_id,
    sa.subaccount_name,
    b.user_id,
    ct.contact_first_name||' '||ct.contact_last_name AS user_name,
    IFF(fo.first_open IS NULL, FALSE, TRUE) AS first_open
FROM base b
    LEFT JOIN royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor v ON b.label_id = v.vendor_id
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country c ON v.country = c.id
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users oa ON v.assigned_to = oa.id
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.subaccount sa ON b.subaccount_id = sa.subaccount_id
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.vend_contact vc ON TRY_TO_NUMERIC(b.user_id) = vc.id
    LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.contact ct ON vc.contact_id = ct.contact_id
    LEFT JOIN first_open fo ON b.timestamp = fo.first_open AND b.user_id = fo.user_id
WHERE v.vendor_id NOT IN (6971, 15063) ;;
```