{
  "start": "01 March 2019",
  "end": "07 March 2019",
  "position": 2,
  "track_name": "Giant",
  "track_uri": "/search/singles/giant/",
  "track_file_name": "search-singles-giant",
  "artist_name": "Calvin Harris & Rag'N'Bone Man",
  "artist_uri": "/artist/54916/calvin-harris-and-ragnbone-man/",
  "label": "Columbia"
}

head -n3 import/items.json | jq '.'

{
  "start": "01 March 2019",
  "end": "07 March 2019",
  "position": 1,
  "track_name": "Someone You Loved",
  "track_uri": "/search/singles/someone-you-loved/",
  "track_file_name": "search-singles-someone-you-loved",
  "artist_name": "Lewis Capaldi",
  "artist_uri": "/artist/51369/lewis-capaldi/",
  "label": "Emi",
  "duration": 182160,
  "artists": [
    {
      "id": "4GNC7GD6oZMSxPGyXy4MNB",
      "name": "Lewis Capaldi"
    }
  ]
}
{
  "start": "01 March 2019",
  "end": "07 March 2019",
  "position": 2,
  "track_name": "Giant",
  "track_uri": "/search/singles/giant/",
  "track_file_name": "search-singles-giant",
  "artist_name": "Calvin Harris & Rag'N'Bone Man",
  "artist_uri": "/artist/54916/calvin-harris-and-ragnbone-man/",
  "label": "Columbia",
  "duration": 229184,
  "artists": [
    {
      "id": "7CajNmpbOovFoOoasH2HaY",
      "name": "Calvin Harris"
    },
    {
      "id": "4f9iBmdUOhQWeP7dcAn1pf",
      "name": "Rag'n'Bone Man"
    }
  ]
}
{
  "start": "01 March 2019",
  "end": "07 March 2019",
  "position": 3,
  "track_name": "Break Up With Your Girlfriend I'M Bored",
  "track_uri": "/search/singles/break-up-with-your-girlfriend-i'm-bored/",
  "track_file_name": "search-singles-break-up-with-your-girlfriend-i'm-bored",
  "artist_name": "Ariana Grande",
  "artist_uri": "/artist/26221/ariana-grande/",
  "label": "Republic Records",
  "duration": 190440,
  "artists": [
    {
      "id": "66CXWjxzNUsdJxJ2JdwvnR",
      "name": "Ariana Grande"
    }
  ]
}

CALL apoc.load.json("https://github.com/mneedham/uk-charts-neo4j/raw/master/import/items.json")
YIELD value
RETURN value
LIMIT 1

CREATE INDEX ON :Chart(start);
CREATE CONSTRAINT ON (l:Label) ASSERT l.name IS UNIQUE;
CREATE CONSTRAINT ON (s:Song) ASSERT s.uri IS UNIQUE;
CREATE CONSTRAINT ON (a:Artist) ASSERT a.id IS UNIQUE;

CALL apoc.load.json("file://items.json")
YIELD value

MERGE (chart:Chart {
  start: date(datetime({epochmillis: apoc.date.parse(value.start, "ms", "dd MMMM yyyy")})),
  end: date(datetime({epochmillis: apoc.date.parse(value.end, "ms", "dd MMMM yyyy")}))
})
MERGE (label:Label {name: value.label})
MERGE (song:Song {uri: value.track_uri})
SET song.title = value.track_name,
    song.duration = CASE WHEN value.duration is null THEN null
                         ELSE duration({milliseconds:value.duration})
                    END

MERGE (song)-[:LABEL]->(label)
MERGE (song)-[inChart:IN_CHART]->(chart)
SET inChart.position = value.position

FOREACH(artist IN value.artists |
  MERGE (a:Artist {id: artist.id})
  SET a.name = artist.name
  MERGE (song)-[:ARTIST]->(a)
);

MATCH (song:Song)-[inChart:IN_CHART {position: 1}]->(chart),
      (song)-[:ARTIST]->(artist)

// order the chart dates so that they show in order when
// we collect them in the RETURN statement
WITH song, collect(artist.name) AS artists, inChart, chart
ORDER BY chart.start

RETURN song.title AS song, artists,
       apoc.date.format(song.duration.milliseconds, 'ms', 'mm:ss') AS duration,
       count(*) AS count, collect(toString(chart.end)) AS weeks
ORDER By count DESC;

MATCH (song:Song)-[inChart:IN_CHART {position: 1}]->(chart),
      (song)-[:ARTIST]->(artist:Artist)
WITH artist, song, count(*) AS weeks
RETURN artist.name,
       apoc.map.fromPairs(collect([song.title, weeks])) AS songs,
       count(*) AS count,
       sum(weeks) AS weeks
ORDER BY weeks DESC;

MATCH (song:Song {title: "South Of The Border"})
MATCH (song)-[inChart:IN_CHART]->(chart)

WITH song, chart, inChart ORDER BY chart.start

RETURN inChart.position AS position,
       collect(toString(chart.end)) AS charts
ORDER BY position

MATCH (artist:Artist {name: "Ed Sheeran"})
MATCH (artist)<-[:ARTIST]-(song)-[inChart:IN_CHART]->(chart)

WITH song, artist,
     min(inChart.position) AS bestPosition,
     toString(min(chart.end)) AS chart
OPTIONAL MATCH (artist)<-[:ARTIST]-(song)-[:ARTIST]->(collaborator)

RETURN song.title AS song,
       collect(collaborator.name) AS collaborators,
       bestPosition, chart
ORDER BY bestPosition

MATCH (artist:Artist {name: "Ed Sheeran"})
MATCH (artist)<-[:ARTIST]-(song)-[inChart:IN_CHART]->(chart)

WITH song, artist,
     min(inChart.position) AS bestPosition,
     toString(min(chart.end)) AS chart

// Create virtual nodes and relationships to represent
// the best chart position for a song
CALL apoc.create.vNode(["Chart"], {end: chart}) YIELD node AS bestChart
CALL apoc.create.vRelationship(song, "IN_CHART", {position: bestPosition}, bestChart) YIELD rel

OPTIONAL MATCH (artist)<-[:ARTIST]-(song)-[:ARTIST]->(collaborator)
RETURN *

MATCH (artist:Artist)
OPTIONAL MATCH (artist)<-[:ARTIST]-(song)-[:ARTIST]->(otherArtist)
WITH artist, count(otherArtist) AS count
RETURN count, count(*)
ORDER BY count

MATCH (chart:Chart)
WITH chart ORDER BY chart.start
WITH collect(chart) AS charts
CALL apoc.nodes.link(charts, "NEXT")
RETURN count(*)

MATCH (song:Song)-[inChart:IN_CHART]->(chart)<-[:NEXT]-(previousChart),
      (previousChart)<-[inChartPrevious:IN_CHART]-(song),
      (song)-[:ARTIST]->(artist)
WITH song, collect(artist.name) AS artists,
     inChartPrevious.position AS firstPosition,
     inChart.position AS secondPosition,
     previousChart, chart
RETURN song.title AS song, artists, firstPosition, secondPosition,
       firstPosition - secondPosition AS change,
       toString(previousChart.end) AS firstChart,
       toString(chart.end) AS secondChart
ORDER BY change DESC
LIMIT 5

// Find songs that charted at position 1
MATCH (song:Song)-[inChart:IN_CHART {position: 1}]->(chart)

// And didn't have an entry on a chart before this one
WHERE not((chart)<-[:NEXT*]-()<-[:IN_CHART]-(song))

WITH song, chart ORDER BY chart.end
RETURN song.title AS song,
       [(song)-[:ARTIST]->(artist) | artist.name] AS artists,
       collect(toString(chart.end)) AS chart

// Find the first week that the song got to number 1
MATCH (song:Song)-[inChart:IN_CHART {position: 1}]->(chart)
WITH song, chart ORDER BY song, chart.end
WITH song, collect(chart)[0] AS firstWeekAtNumber1

// Find the earliest week that the song charted
MATCH (firstWeekAtNumber1)<-[:NEXT*]-(previous)<-[previousInChart:IN_CHART]-(song)
WITH song, firstWeekAtNumber1, previous, previousInChart
ORDER BY song, previous.end
WITH song, firstWeekAtNumber1,
     collect(previousInChart.position)[0] AS firstPosition,
     collect(previous)[0] AS firstWeek

RETURN song.title AS song,
       [(song)-[:ARTIST]->(artist) | artist.name] AS artists,
       toString(firstWeekAtNumber1.end) AS firstWeekAtNumber1,
       firstPosition AS firstPosition,
       toString(firstWeek.end) AS firstWeek,
       [path = (firstWeekAtNumber1)<-[:NEXT*]-(firstWeek) | length(path)][0] AS weeksToReachNumber1