# View: meta_analytics

**View Name:** meta_analytics
**Table Source:** `Derived from: Dbt_Prod.Awal_Tier_Groups, Meta.Staging_Raw_Meta_Consumption, Meta.Staging_Raw_Meta_Production, Prod.Dim_Artist, Prod.Dim_Label + 3 more`
**File Path:** `views/meta_analytics/meta_analytics.view.lkml`

## Overview

- **File Size:** 6585 bytes
- **Lines of Code:** 269
- **Dimensions:** 16
- **Measures:** 3
- **Dimension Groups:** 1
- **Filters:** 0

## Comments & Notes

- # Dimensions
- Change to number if this is purely numeric and you want to avoid string casting
- These represent the pre-aggregated metrics from the derived table SQL

## Dimensions

| Name | Type |
|------|------|
| `country_code` | string |
| `meta_product` | string |
| `parent_company` | string |
| `brand_name` | string |
| `service_tier_name` | string |
| `owner` | string |
| `labelid` | number |
| `labelname` | string |
| `artistname` | string |
| `upc` | string |
| `releasename` | string |
| `isrc` | string |
| `trackname` | string |
| `licensor` | string |
| `views_raw` | number |
| `creations_raw` | number |

## Measures

| Name | Type |
|------|------|
| `count` | count |
| `views` | sum |
| `creations` | sum |

## Dimension Groups

| Name | Type |
|------|------|
| `activity_date` | time |

## SQL Comments

- 633,592,348 rows with no filters
- 133,924,427 rows with no filters
- table to ensure nothing is missed in the base
- 769,516,775 rows with no filters
- grouping table to ensure no duplicates

## Derived Table

```sql
sql:
      with views_and_creations as (
          with views as (
          select
              *
          from consumer_reporting.meta.staging_raw_meta_consumption
          where licensor = 'theorchard'
          -- 633,592,348 rows with no filters
          ),

          creations as (
          select
              *
          from consumer_reporting.meta.staging_raw_meta_production
          where licensor = 'theorchard'
          -- 133,924,427 rows with no filters
          ),

          -- table to ensure nothing is missed in the base
          views_union_creations as (
          select
              activity_date,
              isrc,
              licensor,
              country_code,
              meta_product,
              upc
          from views

          union all

          select
              activity_date,
              isrc,
              licensor,
              country_code,
              meta_product,
              upc
          from creations
          --769,516,775 rows with no filters
          ),

          -- grouping table to ensure no duplicates
          views_and_creations_grouped as (
          select
              *
          from views_union_creations
          group by all
          -- 665,960,917 rows with no filters
          )

          -- adding back in measures (views and creations)
          select
              v.activity_date,
              v.country_code,
              v.meta_product,
              tg.parent_company,
              tg.brand_name,
              tg.service_tier_name,
              tg.owner,
              dr.labelid,
              dl.labelname,
              da.artistname,
              v.upc,
              dr.releasename,
              v.isrc,
              dt.trackname,
              v.licensor,
              sum(vw.views) as views,
              sum(c.creations) as creations
          from views_and_creations_grouped v
          left join views vw on v.activity_date = vw.activity_date
          and v.isrc = vw.isrc
          and v.country_code = vw.country_code
          and IFNULL(v.upc, '') = IFNULL(vw.upc, '')
          and v.meta_product = vw.meta_product
          left join creations c on v.activity_date = c.activity_date
          and v.isrc = c.isrc
          and v.country_code = c.country_code
          and IFNULL(v.upc, '') = IFNULL(c.upc, '')
          and v.meta_product = c.meta_product
          left join facts.prod.dim_release_history drh         -- join to get correct release ownership
              on v.upc = drh.releaseid
              and (v.activity_date >= drh.start_date_inclusive or drh.start_date_inclusive is null)
              and (v.activity_date <= drh.end_date_inclusive or drh.end_date_inclusive is null)


          left join facts.prod.dim_release dr                  -- join to get release metadata info
              on v.upc = dr.releaseid


          left join facts.prod.dim_track_clean_mv dt           -- join to get track level metdata info
              on upper(v.isrc) = upper(dt.isrc)
              and v.upc = dt.upc

          left join facts.prod.dim_artist da                   -- join to get artist name
              on dr.artistid = da.artistid


          left join facts.prod.dim_label dl                    -- join to get label name
              on drh.labelid = dl.labelid


          left join intelligence.dbt_prod.awal_tier_groups tg  -- join to get label tiers and brand info
              on drh.labelid = tg.labelid
          group by all
          )

          select
              *
          from views_and_creations
          --where country_code = 'IN'
          group by all
          order by 1 desc

          -- 665,960,917 rows with no filters and measures applied
          -- 665,960,917 rows with all metadata applied

    ;;
```