In [1]:
%run startup.ipy
In [2]:
label = 'hyperion'
In [3]:
import pandas as pd
pd.set_option('display.max_colwidth', -1)
In [4]:
df = sql("""SELECT id, gid, name, comment FROM label WHERE name IN ('hyperion', 'helios');""")
label_mbid = df.gid.tolist()
df.head()
Out[4]:
Actually I'm checking when "release label" links were added to release, which is most of the time done together.
In [5]:
new_releases = sql("""
SELECT editor.name AS editor,
COUNT(*) AS edit_count
FROM label
JOIN edit_label ON label.id = edit_label.label
JOIN edit ON edit.id = edit_label.edit
JOIN editor ON editor.id = edit.editor
WHERE label.name IN ('hyperion', 'helios')
AND edit.open_time >= '2018-02-01'
GROUP BY editor.name
ORDER BY edit_count DESC
;""")
new_releases
Out[5]:
In [6]:
new_releases.edit_count.sum()
Out[6]:
Find all edits between Feb. 1st and March 14th regarding recordings present on Hyperion/Helios releases
In [7]:
recording_edits = sql("""
SELECT editor.name AS editor,
COUNT(*) AS edit_count
FROM recording AS rec
JOIN edit_recording ON rec.id = edit_recording.recording
JOIN edit ON edit.id = edit_recording.edit
JOIN editor ON editor.id = edit.editor
JOIN track on track.recording = rec.id
JOIN medium AS m ON track.medium = m.id
JOIN release AS r ON m.release = r.id
JOIN release_label AS rl ON rl.release = r.id
JOIN label ON rl.label = label.id
WHERE label.name IN ('hyperion', 'helios')
AND edit.open_time >= '2018-02-01'
GROUP BY editor.name
ORDER BY edit_count DESC
;""")
recording_edits
Out[7]:
In [8]:
recording_edits.edit_count.sum()
Out[8]:
In [9]:
sql("""
SELECT COUNT(*) AS edit_count
FROM release AS r
JOIN edit_release ON r.id = edit_release.release
JOIN edit ON edit.id = edit_release.edit
JOIN release_label AS rl ON rl.release = r.id
JOIN label ON rl.label = label.id
WHERE label.name IN ('hyperion', 'helios')
AND edit.open_time >= '2018-02-01'
AND edit.type = 314
;""")
Out[9]:
NB: Phonographic copyright ℗ should use 'Hyperion Records Ltd', not Hyperion or Helios
In [10]:
sql("""
SELECT COUNT(*) AS edit_count
FROM recording AS r
JOIN edit_recording ON r.id = edit_recording.recording
JOIN edit ON edit.id = edit_recording.edit
JOIN l_label_recording AS lar ON r.id = lar.entity1
JOIN label ON label.id = lar.entity0
JOIN link ON link.id = lar.link
WHERE label.name = 'Hyperion Records Ltd, London'
AND edit.open_time >= '2018-02-01'
AND link.link_type = 867
;""")
Out[10]:
Relation created for this community sprint if I'm not mistaken
In [11]:
sql("""
SELECT a.name, COUNT(*) AS cnt
FROM release AS r
JOIN l_artist_release AS lar ON r.id = lar.entity1
JOIN artist AS a ON a.id = lar.entity0
JOIN link AS l ON l.id = lar.link
WHERE l.link_type=929
GROUP BY a.name
ORDER BY cnt DESC
LIMIT 5
""")
Out[11]:
4 of those 5 work for Hyperion
In [13]:
sql("""
SELECT a.name, COUNT(*) AS cnt
FROM recording AS r
JOIN l_artist_recording AS lar ON r.id = lar.entity1
JOIN artist AS a ON a.id = lar.entity0
JOIN link AS l ON l.id = lar.link
WHERE l.link_type=128
GROUP BY a.name
ORDER BY cnt DESC
LIMIT 20
""")
Out[13]:
In [14]:
sql("""
SELECT a.name, COUNT(*) AS cnt
FROM recording AS r
JOIN l_artist_recording AS lar ON r.id = lar.entity1
JOIN artist AS a ON a.id = lar.entity0
JOIN link AS l ON l.id = lar.link
WHERE l.link_type=141
GROUP BY a.name
ORDER BY cnt DESC
LIMIT 20
""")
Out[14]: