Index: music/urls.py
===================================================================
--- music/urls.py (revision bfca48bdd486ce59ee78a865790e30637555f1ce)
+++ music/urls.py (revision 17ed1daeee62a5359f4cac763228db2fbc1287c1)
@@ -9,5 +9,6 @@
path('artist/most-popular', views.rank_list_artists, name='rank_list_artists'),
path('track/', views.track_list, name='track_list'),
- path('customer/genre', views.most_listened_genre_per_customer, name='most_listened_genre_per_customer'),
+ path('customer/genres-per-customer', views.genres_per_customer, name='genres_per_customer'),
+ path('customer/most-popular-genre-per-customer', views.most_listened_genre_per_customer, name='most_listened_genre_per_customer'),
path('media-type/percentage', views.media_type_percentage, name='media_type_percentage'),
path('track/per-genre', views.tracks_count_per_genre, name='track_count_per_genre'),
Index: music/views.py
===================================================================
--- music/views.py (revision bfca48bdd486ce59ee78a865790e30637555f1ce)
+++ music/views.py (revision 17ed1daeee62a5359f4cac763228db2fbc1287c1)
@@ -84,5 +84,4 @@
-
def media_type_percentage(request):
with connection.cursor() as cursor:
@@ -103,2 +102,33 @@
return render(request, 'most_listened_genre_per_customer.html', {'data': data})
+
+
+def genres_per_customer(request):
+ customers = Customer.objects.all()
+ selected_customer_id = request.GET.get('customer_id')
+ data = []
+
+ if selected_customer_id:
+ query = """
+ SELECT c.first_name as first_name, c.last_name as last_name, g.name as genre, COUNT(tr.track_id) as track_count
+ FROM customer c
+ LEFT JOIN invoice i ON c.customer_id = i.customer_id
+ LEFT JOIN invoice_line il ON i.invoice_id = il.invoice_id
+ LEFT JOIN track tr ON il.track_id = tr.track_id
+ LEFT JOIN genre g ON tr.genre_id = g.genre_id
+ WHERE c.customer_id = %s
+ GROUP BY c.customer_id, c.first_name, c.last_name, g.genre_id, g.name
+ ORDER BY c.first_name
+ """
+
+ with connection.cursor() as cursor:
+ cursor.execute(query, [selected_customer_id])
+ rows = cursor.fetchall()
+ data = [{'first_name': row[0], 'last_name': row[1], 'genre': row[2], 'track_count': row[3]} for row in rows]
+
+
+ return render(request, 'genres_per_customer.html', {
+ 'customers': customers,
+ 'data': data,
+ 'selected_customer_id': selected_customer_id,
+ })
Index: templates/genres_per_customer.html
===================================================================
--- templates/genres_per_customer.html (revision 17ed1daeee62a5359f4cac763228db2fbc1287c1)
+++ templates/genres_per_customer.html (revision 17ed1daeee62a5359f4cac763228db2fbc1287c1)
@@ -0,0 +1,33 @@
+
+
+
+ Number of tracks from each genre per Customer
+
+
+Number of tracks from each genre per Customer
+
+
+
+First Name Last Name - Genre - Num of Tracks
+
+
+ {% for row in data %}
+ -
+ {{ row.first_name }} - {{ row.last_name }} - {{ row.genre }} - {{ row.track_count }}
+ {% endfor %}
+
+
+
+