# View: dt_itunes_complete_month_by_service

**View Name:** dt_itunes_complete_month_by_service
**Table Source:** `Derived from: Dig_Sales_Analytics`
**File Path:** `dt_itunes_complete_month_by_service.view.lkml`

## Overview

- **File Size:** 1454 bytes
- **Lines of Code:** 59
- **Dimensions:** 3
- **Measures:** 1
- **Dimension Groups:** 0
- **Filters:** 0

## Comments & Notes

- Regular schedule files ^^

## Dimensions

| Name | Type |
|------|------|
| `date` | string |
| `service` | string |
| `total_usd` | string |

## Measures

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

## Derived Table

```sql
sql: SELECT
       dsa.date,
       CASE
        WHEN dsa.trans_type = "CL" THEN 'Match'
        WHEN dsa.trans_type IN ("S", "SV") THEN 'Apple Music'
        WHEN dsa.trans_type = "AS" THEN 'Beats1'
        WHEN dsa.trans_type = "NR" THEN 'Radio'
        ELSE 'Downloads'
       END AS `service`,
       CONCAT('$', FORMAT(SUM(dsa.total_revenue),2)) AS `total_usd`
      FROM dig_sales_analytics dsa
      WHERE dsa.`customer_master_master_id` = 1
      AND dsa.date >= DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL -1 MONTH)
      GROUP BY dsa.`date` DESC, `service`
      # Regular schedule files ^^

      UNION

      SELECT
       '' AS `date`,
       'Total' AS `service`,
       CONCAT('$', FORMAT(x.total, 2)) AS `total_usd`
       FROM
       (SELECT
          SUM(dsa.total_revenue) AS `total`
         FROM dig_sales_analytics dsa
         WHERE dsa.`customer_master_master_id` = 1
         AND dsa.date >= DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL -1 MONTH)
       ) `x`
       # Totals ^^
       ;;
```