# View: chartmetric_perf_marketshare

**View Name:** chartmetric_perf_marketshare
**Table Source:** `Derived from: Dbt_Hstanhope_Knr.Chartmetric_Perf_Content_Income, Intelligence.Dbt_Hstanhope_Knr, Ranked_Tracks, Track_Stats`
**File Path:** `views/chartmetric_perf_marketshare.view.lkml`

## Overview

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

## Comments & Notes

- ################################################################
- 1) Top-N parameter for “show me the top X tracks by play_count”
- ################################################################

## Dimensions

| Name | Type |
|------|------|
| `isrc` | string |

## Measures

| Name | Type |
|------|------|
| `average_share_fraction` | number |
| `sum_share_fraction` | sum |

## SQL Comments

- pull a single row per ISRC/country/year, grabbing the max(play_count)
- rank them and compute the share fraction
- pick only the top N

## Derived Table

```sql
sql:
      WITH
      -- pull a single row per ISRC/country/year, grabbing the max(play_count)
      track_stats AS (
        SELECT
          isrc,
          country_name,
          year,
          MAX(play_count)              AS play_count,
          MAX(cm_track_id)             AS cm_track_id,
          MAX(cm_track_title)          AS cm_track_title,
          MAX(cm_album_name)           AS cm_album_name,
          MAX(cm_artist_name)          AS cm_artist_name,
          MAX(cm_track_duration)       AS cm_track_duration,
          MAX(label)                   AS label,
          MAX(release_date)            AS release_date,
          MAX(total_fa_count)          AS total_fa_count,
          MAX(number_of_primary_performers) AS number_of_primary_performers
        FROM intelligence.dbt_hstanhope_knr.chartmetric_perf_content_income
        WHERE isrc IS NOT NULL
          AND TRIM(isrc) <> ''
        GROUP BY 1,2,3
      ),

      -- rank them and compute the share fraction
      ranked_tracks AS (
      SELECT
      ts.*,
      CASE
      WHEN ts.number_of_primary_performers > 0
      THEN ts.total_fa_count::FLOAT
      / ts.number_of_primary_performers
      ELSE 0
      END                                    AS share_fraction,
      ROW_NUMBER() OVER (
      PARTITION BY ts.country_name, ts.year
      ORDER BY ts.play_count DESC
      )                                       AS play_rank
      FROM track_stats ts
      )

      -- pick only the top N
      SELECT *
      FROM ranked_tracks
      WHERE play_rank <= {% parameter top_n %
```