# View: contact_created_date

**View Name:** contact_created_date
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Contact, Art_Relations_Prod_Art_Relations.Orchadmin_Users, Art_Relations_Prod_Art_Relations.Owner, Art_Relations_Prod_Art_Relations.Vend_Contact, Dbt_Prod.Dt_Access_Filter_1091 + 3 more`
**File Path:** `contact_created_date.view.lkml`

## Overview

- **File Size:** 6163 bytes
- **Lines of Code:** 140
- **Dimensions:** 10
- **Measures:** 1
- **Dimension Groups:** 0
- **Filters:** 0

## Comments & Notes

- dimension: fact_prod_profile_Profile_name {
- type: string
- sql: ${TABLE}."fact_prod_profile.Profile_name" ;;

## Dimensions

| Name | Type |
|------|------|
| `orch_app_ar_vendor_vendor_id` | number |
| `fact_prod_profile_updated_on_1` | date_time |
| `union_contact_alt_email_contact_email` | string |
| `label_manager_orchadmin_user` | string |
| `quarterback_label_manager_orchadmin_user` | string |
| `orch_app_ar_vend_contact_contact_is_active` | string |
| `orch_app_ar_vend_contact_contact_is_master` | string |
| `fact_prod_profile_last_modified_at_1` | date_time |
| `fact_prod_profile_created_at_1` | date_time |
| `ranking` | number |

## Measures

| Name | Type |
|------|------|
| `count` | count |

## SQL Comments

- fact_prod_profile.Profile_name AS "fact_prod_profile.Profile_name",
- "fact_prod_profile.last_modified_at_1" desc, "fact_prod_profile.created_at_1" desc
- INNER JOIN intelligence.dbt_prod.dt_access_filter_1091  AS dt_access_filter_1091 ON orch_app_ar_vendor.vendor_id = dt_access_filter_1091.vendor_id
- AND fact_prod_profile.Profile_name = 'LabelProfile'
- WHERE  (dt_access_filter_1091.access_control ) = 0

## Derived Table

```sql
sql: WITH union_contact_alt_email AS (SELECT DISTINCT
                  c1.contact_id,
                  c1.contact_email,
                  'N' AS is_alt
                FROM orchard_app_reporting_v2.art_relations_prod_art_relations.contact c1
                WHERE c1.contact_email IS NOT NULL
                  AND c1.contact_email != ''

                UNION ALL

                SELECT DISTINCT
                  c2.contact_id,
                  c2.alt_email,
                  'Y' AS is_alt
                FROM orchard_app_reporting_v2.art_relations_prod_art_relations.contact c2
                WHERE c2.alt_email IS NOT NULL
                  AND c2.alt_email != ''
                )
      SELECT
       --fact_prod_profile.Profile_name AS "fact_prod_profile.Profile_name",
          orch_app_ar_vendor.vendor_id  AS "orch_app_ar_vendor.vendor_id",
              CAST((TO_CHAR(DATE_TRUNC('second', fact_prod_profile.Updated_on), 'YYYY-MM-DD HH24:MI:SS')) AS DATETIME) AS "fact_prod_profile.updated_on_1",
          union_contact_alt_email.contact_email  AS "union_contact_alt_email.contact_email",
          label_manager.f_name||' '||label_manager.l_name  AS "label_manager.orchadmin_user",
          quarterback_label_manager.f_name||' '||quarterback_label_manager.l_name  AS "quarterback_label_manager.orchadmin_user",
              (CASE WHEN IFF(orch_app_ar_vend_contact.active = 'Y', TRUE, FALSE)  THEN 'Yes' ELSE 'No' END) AS "orch_app_ar_vend_contact.contact_is_active",
              (CASE WHEN IFF(orch_app_ar_vend_contact.master = 'Y', TRUE, FALSE)  THEN 'Yes' ELSE 'No' END) AS "orch_app_ar_vend_contact.contact_is_master",
              CAST((TO_CHAR(DATE_TRUNC('second', fact_prod_profile.Last_modified_at), 'YYYY-MM-DD HH24:MI:SS')) AS DATETIME) AS "fact_prod_profile.last_modified_at_1",
              CAST((TO_CHAR(DATE_TRUNC('second', fact_prod_profile.Created_at), 'YYYY-MM-DD HH24:MI:SS')) AS DATETIME) AS "fact_prod_profile.created_at_1",
              rank () OVER (partition by orch_app_ar_vendor.vendor_id order by "fact_prod_profile.updated_on_1" desc  ) as ranking
           -- "fact_prod_profile.last_modified_at_1" desc, "fact_prod_profile.created_at_1" desc
      FROM orchard_app_reporting_v2.art_relations_prod_art_relations.owner  AS orch_app_ar_owner
      INNER JOIN royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor  AS orch_app_ar_vendor ON lower(orch_app_ar_owner.owner_abbrivation) = lower(orch_app_ar_vendor.owner)
      --INNER JOIN intelligence.dbt_prod.dt_access_filter_1091  AS dt_access_filter_1091 ON orch_app_ar_vendor.vendor_id = dt_access_filter_1091.vendor_id
      LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users  AS label_manager ON orch_app_ar_vendor.assigned_to = label_manager.id
      LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users  AS quarterback_label_manager ON orch_app_ar_vendor.quarterback_label_manager = quarterback_label_manager.id
      LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.vend_contact  AS orch_app_ar_vend_contact ON orch_app_ar_vendor.vendor_id = orch_app_ar_vend_contact.vendor_id
            AND orch_app_ar_vend_contact.subaccount_id IS NULL
      LEFT JOIN facts.prod.profile AS fact_prod_profile ON orch_app_ar_vend_contact.id =  fact_prod_profile.Profile_id
          --AND fact_prod_profile.Profile_name = 'LabelProfile'
      LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.contact  AS orch_app_ar_contact ON orch_app_ar_vend_contact.contact_id = orch_app_ar_contact.contact_id
      LEFT JOIN union_contact_alt_email ON orch_app_ar_contact.contact_id = union_contact_alt_email.contact_id
     -- WHERE  (dt_access_filter_1091.access_control ) = 0
        WHERE fact_prod_profile.Profile_type = 'LabelProfile'
        AND fact_prod_profile.Updated_on IS NOT NULL
      GROUP BY
          (DATE_TRUNC('second', fact_prod_profile.Updated_on)),
          (DATE_TRUNC('second', fact_prod_profile.Created_at)),
          (DATE_TRUNC('second', fact_prod_profile.Last_modified_at)),
          1,
          3,
          4,
          5,
          6,
          7

      ORDER BY
          1 DESC,
          2 DESC
      ;;
```