# View: dt_gross_revenue_active_products

**View Name:** dt_gross_revenue_active_products
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Artist_Info, Art_Relations_Prod_Art_Relations.Owner, Art_Relations_Prod_Art_Relations.Releases, Prod.Abacus_Fact_Sales_Unified_Dbt, Prod.Vw_Dim_Abacus_Ar_Vendor + 3 more`
**File Path:** `views/dt_gross_revenue_active_products.view.lkml`

## Overview

- **File Size:** 3157 bytes
- **Lines of Code:** 85
- **Dimensions:** 1
- **Measures:** 2
- **Dimension Groups:** 1
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `label_id` | string |

## Measures

| Name | Type |
|------|------|
| `monthly_upc` | sum |
| `monthly_gross_revenue_usd` | sum |

## Dimension Groups

| Name | Type |
|------|------|
| `statement_month` | time |

## Derived Table

```sql
sql:
     WITH accounting AS (
        SELECT
            a.account_id,
            DATE_FROM_PARTS(sp.statement_year, sp.statement_month, 1) AS statement_month,
            ROUND(SUM(CASE
                WHEN a.sale_currency_code = 'USD' THEN a.gross_revenue_sale_currency
                WHEN a.sale_currency_code != 'USD' AND er.to_currency_code = 'USD' THEN a.gross_revenue_sale_currency * er.rate
                ELSE 0
            END), 2) AS monthly_gross_revenue_usd
        FROM royalty_accounting.prod.abacus_fact_sales_unified_dbt a
        LEFT JOIN ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.EXCHANGE_RATE er
            ON a.statement_period_id = er.statement_period_id
            AND a.sale_currency_code = er.from_currency_code
            AND er.to_currency_code = 'USD'
        LEFT JOIN ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.STATEMENT_PERIOD sp
            ON a.statement_period_id = sp.statement_period_id
        WHERE DATE_FROM_PARTS(sp.statement_year, sp.statement_month, 1) IS NOT NULL
        GROUP BY ALL
    ),

      metadata AS (
      SELECT
      orch_app_ar_vendor.vendor_id AS label_id,
      DATE_TRUNC('month', CAST(orch_app_ar_releases.sale_start_date AS DATE)) AS sale_start_month,
      COUNT(DISTINCT orch_app_ar_releases.upc) AS monthly_upc
      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)
      LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.artist_info AS orch_app_ar_artist_info
      ON orch_app_ar_vendor.vendor_id = orch_app_ar_artist_info.vendor_id
      LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.releases AS orch_app_ar_releases
      ON orch_app_ar_artist_info.artist_id = orch_app_ar_releases.artist_id
      WHERE orch_app_ar_releases.release_status = 'in_content'
      AND orch_app_ar_releases.deletions = 'N'
      AND orch_app_ar_releases.not_for_distribution = 'N'
      AND orch_app_ar_releases.sale_start_date <= CURRENT_DATE
      GROUP BY ALL
      )

      SELECT
      m.label_id,
      m.sale_start_month AS statement_month,
      m.monthly_upc,
      a.monthly_gross_revenue_usd
      FROM metadata m
      LEFT JOIN accounting a
      ON m.label_id = a.account_id
      AND m.sale_start_month = a.statement_month
      ;;
```