# DATE
Users are typically interested in activity over a window of time.

Use the `DOWNLOAD_ACTIVITY_DATE` column, which is in the YYYY-MM-DD format.

Users will ask you about things like "in the last week", or "in the last month". Instead of inserting placeholders
into the generated sql query, calculate the start and end dates and insert those into the generated sql query.

# TERRITORIES
Our users use the word Territory/Territories interchangeable with Country/Countries, Market/Markets.

Sometimes a country will be referenced or asked about in a prompt from the user. Convert this to its ISO 3166 ALPHA-2 Code and use with the COUNTRY_CODE field, when asked to filter or constrain searches by a country. If you do this, describe that this is how you got the country code. For example, "Japan" (or "japan") would require you to use "JP" on COUNTRY_CODE.

# STORES
Our users will want information per store sometimes. Use STORE_NAME column for that search, and in that case, do a case-insensitive string comparison (`ILIKE`) in case the user's prompt does not match what was stored in the field.

# TRACK STREAMING ANALYTICS DATA
This document describes a Snowflake table that should be used for analyzing the number of times a track was streamed, the user's interaction with the stream (streamed, skipped, saved), the subscription type of users listening to the stream,
how the user ended up listening (SOS), and more.

We describe unique tracks by their ISRC, Performers, and Track Name. An ISRC can appear in our data with multiple TRACK_ID values because TRACK_ID is used to describe the instance of the ISRC on a given album, or PRODUCT_ID. Every track has an ISRC, but not every track has a TRACK_ID or a LABEL_NAME.

In the context of our database, SOS stands for Source of Streams. This is a term of art referring to how a listener
ended up streaming the track; this can help provide understanding on how/why people end up listening to a track.

Another thing of interest are the number of times the track is skipped vs saved. This is valuable to understand because we
can expect more revenue when a track is not skipped; and more potential listens (and thus, revenue) when a track is saved.

We also care about Subscription Types (SUB_TYPE_*) -- this helps us understand different tiers of listeners.

The database name is `DEV`.
The schema is `DEV_JESS`.
The table is `FLAT_STREAMS`

Here is a list of the columns in `DEV.DEV_JESS.FLAT_STREAMS`, followed by a COMMENT, which contains a description of the column:

TRACK_ID COMMENT 'Internal identifier for the track'
DOWNLOAD_ACTIVITY_DATE COMMENT 'Date of the activity/stream in YYYY-MM-DD format'
COUNTRY_CODE COMMENT 'Two-letter code for country where stream occurred'
FEED_ID COMMENT 'Internal identifier for the feed'
DISTRIBUTOR COMMENT 'Internal identifier for the distributor'
STORE_ID COMMENT 'Internal identifier for the store'
PRODUCT_ID COMMENT 'Internal identifier for the album'
LABEL_ID COMMENT 'Internal identifier for the label'
ISRC COMMENT 'Universal identifier for the track'
STREAMS COMMENT 'Number of times streamed, total (no slicing of data by other conditions)'
SKIPS COMMENT 'Number of times skipped'
SAVES COMMENT 'Number of times saved'
STREAMS_PASSIVE COMMENT 'Passive streams are from listeners via a radio station, algorithmic playlist or platform editorial.'
STREAMS_ACTIVE COMMENT 'Active streams are from listeners who actively sought out the track through search, or played it from their library of saved tracks.'
STREAMS_COLLECTION COMMENT ''
SUB_TYPE_SUBSCRIPTION COMMENT 'Number of streams which were tied to Subscribers'
SUB_TYPE_ADSUPPORTED COMMENT 'Number of streams which were tied to Ad-Supported Listeners'
SUB_TYPE_MIDTIER COMMENT 'Number of streams which were tied to Mid-Tier Listeners'
STREAMS_YOUTUBE_AD_DISABLED_AUDIO COMMENT 'Number of Youtube audio streams where advertising was disabled'
STREAMS_YOUTUBE_AD_DISABLED_VIDEO COMMENT 'Number of Youtube video streams where advertising was disabled'
STREAMS_YOUTUBE_AD_ENABLED_AUDIO COMMENT 'Number of Youtube audio streams where advertising was enabled'
STREAMS_YOUTUBE_AD_ENABLED_VIDEO COMMENT 'Number of Youtube video streams where advertising was enabled'
STREAMS_SOS_SPOTIFY_COLLECTION COMMENT 'Number of streams where the source is a Spotify Collection'
STREAMS_SOS_SPOTIFY_PLAYQUEUE COMMENT 'Number of streams where the source is a Spotify PlayQueue'
STREAMS_SOS_SPOTIFY_ALBUMPAGE COMMENT 'Number of streams where the source is a Spotify Album Page'
STREAMS_SOS_SPOTIFY_ARTISTPAGE COMMENT 'Number of streams where the source is a Spotify Artist Page'
STREAMS_SOS_SPOTIFY_SEARCH COMMENT 'Number of streams where the source is a Spotify Search'
STREAMS_SOS_SPOTIFY_PLAYLISTS COMMENT 'Number of streams where the source is a Spotify Playlist'
STREAMS_SOS_SPOTIFY_RELEASERADAR COMMENT 'Number of streams where the source is Spotify Release Radar'
STREAMS_SOS_SPOTIFY_DISCOVERWEEKLY COMMENT 'Number of streams where the source is Spotify Discover Weekly'
STREAMS_SOS_SPOTIFY_RADIO COMMENT 'Number of streams where the source is Spotify Radio'
STREAMS_SOS_SPOTIFY_DAILYMIX COMMENT 'Number of streams where the source is Spotify Daily Mix'
STREAMS_SOS_SPOTIFY_CHART COMMENT 'Number of streams where the source is a Spotify Chart'
STREAMS_SOS_SPOTIFY_OTHER COMMENT 'Number of streams where the source is something else from Spotify'
STREAMS_SOS_APPLE_LIBRARY COMMENT 'Number of streams where the source is an Apple Library'
STREAMS_SOS_APPLE_EXTERNAL COMMENT 'Number of streams where the source is an Apple external link'
STREAMS_SOS_APPLE_VOICE COMMENT 'Number of streams where the source is an Apple voice what'
STREAMS_SOS_APPLE_SEARCH COMMENT 'Number of streams where the source is an Apple Search'
STREAMS_SOS_APPLE_MUSICKIT COMMENT 'Number of streams where the source is an Apple Music Kit'
STREAMS_SOS_APPLE_NOWPLAYING COMMENT 'Number of streams where the source is Apple Now Playing'
STREAMS_SOS_APPLE_DISCOVERY COMMENT 'Number of streams where the source is Apple Discovery'
STREAMS_SOS_APPLE_OTHER COMMENT 'Number of streams where the source is something else from Apple'
STREAMS_SOS_AMAZON_USERPLAYLIST COMMENT 'Number of streams where the source is an Amazon User Playlist'
STREAMS_SOS_AMAZON_SONGS COMMENT 'Number of streams where the source is an Amazon Songs page'
STREAMS_SOS_AMAZON_ALBUM COMMENT 'Number of streams where the source is an Amazon Album page'
STREAMS_SOS_AMAZON_ARTIST COMMENT 'Number of streams where the source is an Amazon Artist page'
STREAMS_SOS_AMAZON_SEARCH COMMENT 'Number of streams where the source is an Amazon Search'
STREAMS_SOS_AMAZON_PLAYLIST COMMENT 'Number of streams where the source is an Amazon Playlist'
STREAMS_SOS_AMAZON_STATION COMMENT 'Number of streams where the source is an Amazon Station'
TRACK_NAME COMMENT 'Name of the track'
UPC COMMENT 'Universal identifier for the album te track appears on'
PERFORMERS COMMENT 'List of performers, no particular order'
LABEL_NAME COMMENT 'If SME distributes the track, which label'
OWNER COMMENT 'Internal identifier for label (oa) owner'
COUNTRY_NAME COMMENT 'Country where the track was streamed'
CONTINENT COMMENT 'Continent where the track was streamed'
STORE_NAME COMMENT 'Streaming service'
FEED_NAME COMMENT 'Feed name'