Changes between Version 16 and Version 17 of AdvancedReports


Ignore:
Timestamp:
02/14/24 13:53:10 (4 months ago)
Author:
185022
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • AdvancedReports

    v16 v17  
    11= Напредни извештаи
    2 == Најслушана песна од еден корисник во минатата година
    3   {{{#!sql
    4 select s.song_id,s.song_title, count(creation_id) as play_count
    5     from(select
    6         from listens_to l join song s on l.creation_id=s.song_id
    7         where l.user_id=current_user_id)
    8 where extract(year from listens_timestamp)=extract(year from current_date)
    9 group by s.song_id,s.song_title
    10 order by play_count desc
    11 limit 1;
    12   }}}
    13 == Жанр на песна кој ''слушателот'' го слушал најмногу минатата година
    14   {{{#!sql
    15 select res.genre_name,count(res.genre_name) as genre_count
    16 from ((select *
    17         from (select *
    18                 from listens_to lt join song s on lt.creation_id =s.song_id) as songs join is_of_genre iog on songs.song_id=iog.creation_id
    19                 ) as is_genre join genre g on is_genre.genre_id=g.genre_id) as res
    20 group by res.genre_name
    21 order by genre_count desc
    22 limit 1;
    23   }}}
    24 == Листа на локации каде еден ''уметник'' имал концерт минатата година
    25   {{{#!sql
    26 -- Ова е само пример доколку бараниот уметник има id 4
    27 select *
    28 from creates_event ce join (select b.event_id as event_id,event_name,concert_location,concert_timedate
    29 from buzzevent b join concert c on b.event_id=c.event_id) as q1 on ce.event_id=q1.event_id
    30 where artist_id = 4 and (concert_timedate >= date_trunc('year',current_date - interval '1' year)
    31                                                      and concert_timedate<date_trunc('year',current_date));
    32   }}}
    33 == Најслушани песни во последните 3 месеци
    34   {{{#!sql
    35 select song_id,song_title,count(*) as listen_count
    36 from (select *
    37       from song s join creation c on s.song_id=c.creation_id) as songs
    38       join listens_to as lt on songs.song_id=lt.creation_id
    39 where listens_timestamp>=current_date-interval '3 months'
    40 group by song_id,song_title
    41 order by listen_count desc;
    42   }}}
    43 == Најпопуларна категорија последниот месец
    44   {{{#!sql
    45 select category_name
    46 from (select res1.episode_id,res1.episode_title,res1.podcast_id,res1.listen_count,category_id
    47 from (select episode_id,episode_title,podcast_id, count(*) as listen_count
    48 from (select *
    49                 from episode e join creation c on e.episode_id=c.creation_id) as ep join listens_to as lt on ep.episode_id=lt.creation_id
    50 where lt.listens_timestamp > current_date-interval'1 month'
    51 group by episode_id,episode_title,podcast_id
    52 order by listen_count desc
    53 limit 1
    54 ) as res1 join podcast p on res1.podcast_id=p.podcast_id) as res2 join category cat on res2.category_id=cat.category_id;
    55   }}}
    56 == Најслушан ''уметник'' во една земја минатата година
    57   {{{#!sql
    58 select b2.user_id,b2.user_name,count(b2.user_name) as listened_artist
    59 from buzzer b1 right join listener l on b1.user_id=l.user_id
    60 join listens_to lt on lt.user_id=l.user_id
    61 join song s on lt.creation_id=s.song_id
    62 join creation c on s.song_id=c.creation_id
    63 join buzzer b2 on c.created_by=b2.user_id
    64 where b1.user_country='MK' and (lt.listens_timestamp >=date_trunc('year',current_date-interval '1' year) and lt.listens_timestamp<date_trunc('year',current_date))
    65 group by b2.user_id,b2.user_name
    66 order by listened_artist desc
    67 limit 1;
    68   }}}
    69 == 10 најдодавани песни во различни плејлисти
    70   {{{#!sql
    71 select s.song_id,s.song_title,count(*) as song_count
    72 from playlist p join in_playlist ip on p.playlist_id=ip.playlist_id
    73 join song s on ip.creation_id=s.song_id
    74 group by s.song_id,s.song_title
    75 order by song_count desc
    76 limit 10;
    77   }}}
    782== Да се излистаат сите албуми според бројот на песни, датумот на објавување и вкупното времетраење
    793  {{{#!sql
    80 
     4SELECT
     5    A.album_id,
     6    A.album_title,
     7    A.album_date,
     8    COUNT(S.song_id) AS number_of_songs,
     9    SUM(C.creation_duration) AS total_duration
     10FROM
     11    ALBUM A
     12JOIN
     13    SONG S ON A.album_id = S.album_id
     14JOIN
     15    CREATION C ON S.creation_id = C.creation_id
     16GROUP BY
     17    A.album_id, A.album_title, A.album_date
     18ORDER BY
     19    number_of_songs DESC;
    8120  }}}
    8221== Да се излистаат сите подкасти кои што имаат повеќе од 60% позитивни реакции, притоа бројот на коментари да биде поголем од 50
    8322  {{{#!sql
     23SELECT
     24    P.podcast_id,
     25    P.podcast_title,
     26    COUNT(DISTINCT BC.comment_id) AS number_of_comments,
     27    COUNT(DISTINCT RW.reaction_id) AS number_of_reactions,
     28    (COUNT(DISTINCT RW.reaction_id) / COUNT(DISTINCT BC.comment_id)) * 100 AS positive_reaction_percentage
     29FROM
     30    PODCAST P
     31JOIN
     32    BUZZCOMMENT BC ON P.podcast_id = BC.commented_on
     33JOIN
     34    REACTS_WITH RW ON BC.comment_id = RW.user_id
     35WHERE
     36    BC.comment_rating > 0
     37GROUP BY
     38    P.podcast_id, P.podcast_title
     39HAVING
     40    (COUNT(DISTINCT RW.reaction_id) / COUNT(DISTINCT BC.comment_id)) * 100 > 60
     41    AND COUNT(DISTINCT BC.comment_id) > 50;
    8442  }}}
    8543== Да се групираат песните/албумите/подкастите според потеклото на мнозинството слушатели
    8644  {{{#!sql
     45WITH CreationListenerOrigin AS (
     46    SELECT
     47        C.creation_id,
     48        L.listener_id,
     49        MAX(L.listener_origin) AS majority_origin
     50    FROM
     51        (
     52            SELECT song_id AS creation_id FROM SONG
     53            UNION
     54            SELECT album_id AS creation_id FROM ALBUM
     55            UNION
     56            SELECT podcast_id AS creation_id FROM PODCAST
     57        ) C
     58    JOIN
     59        LISTENS_TO L ON C.creation_id = L.creation_id
     60    GROUP BY
     61        C.creation_id, L.listener_id
     62)
     63
     64SELECT
     65    CLO.creation_id,
     66    CLO.majority_origin,
     67    CASE
     68        WHEN S.song_id IS NOT NULL THEN 'SONG'
     69        WHEN A.album_id IS NOT NULL THEN 'ALBUM'
     70        WHEN P.podcast_id IS NOT NULL THEN 'PODCAST'
     71    END AS creation_type
     72FROM
     73    CreationListenerOrigin CLO
     74LEFT JOIN
     75    SONG S ON CLO.creation_id = S.song_id
     76LEFT JOIN
     77    ALBUM A ON CLO.creation_id = A.album_id
     78LEFT JOIN
     79    PODCAST P ON CLO.creation_id = P.podcast_id
     80ORDER BY
     81    CLO.creation_id;
    8782  }}}
    8883== Да се најдат жанровите кои најмногу се слушани во определен период од годината
    8984  {{{#!sql
     85SELECT
     86    G.genre_id,
     87    G.genre_name,
     88    COUNT(*) AS listen_count
     89FROM
     90    LISTENS_TO L
     91JOIN
     92    SONG S ON L.creation_id = S.song_id
     93JOIN
     94    GENRE G ON S.genre_id = G.genre_id
     95WHERE
     96    EXTRACT(MONTH FROM L.listens_timestamp) = <месец> AND EXTRACT(YEAR FROM L.listens_timestamp) = <година>
     97GROUP BY
     98    G.genre_id, G.genre_name
     99ORDER BY
     100    listen_count DESC;
    90101  }}}
     102== Прикажи ги сите песни кои имаат просечен рејтинг поголем од 4.5 и се класификуваат како 'поп' или 'рок', а имаат најмалку 100000 преслушувања.
     103  {{{#!sql
     104SELECT song_name, AVG(rating) AS average_rating, COUNT(streams) AS number_of_streams
     105FROM songs
     106JOIN ratings ON songs.song_id = ratings.song_id
     107JOIN classifications ON songs.song_id = classifications.song_id
     108WHERE classifications.genre IN ('pop', 'rock')
     109GROUP BY song_name
     110HAVING average_rating > 4.5 AND number_of_streams > 100000;
     111  }}}
     112== Подкасти кои имаат најмалку 50 епизоди и се класификуваат како образовни
     113  {{{#!sql
     114SELECT p.podcast_title, COUNT(e.episode_id) AS total_episodes
     115FROM PODCAST p
     116JOIN EPISODE e ON p.podcast_id = e.podcast_id
     117JOIN CATEGORY c ON p.category_id = c.category_id
     118WHERE c.category_name = 'Education'
     119GROUP BY p.podcast_id, p.podcast_title
     120HAVING COUNT(e.episode_id) > 50;
     121  }}}
     122== Концерти кои ќе се одржат во Лондон и ќе вклучуваат информации за изведувачите, времетраењето на настанот и бројот на преслушувања на нивните песни:
     123  {{{#!sql
     124SELECT
     125    c.concert_name,
     126    c.concert_location,
     127    c.concert_timeDate,
     128    COUNT(l.song_id) AS total_listens,
     129    a.information AS artist_information
     130FROM CONCERT c
     131JOIN BUZZEVENT b ON c.event_id = b.event_id
     132JOIN ARTIST a ON b.user_id = a.user_id
     133JOIN SONG s ON a.user_id = s.created_by
     134JOIN LISTENS l ON s.song_id = l.song_id
     135WHERE c.concert_location = 'London'
     136GROUP BY c.concert_name, c.concert_location, c.concert_timeDate, a.information
     137ORDER BY c.concert_timeDate;
     138  }}}
     139== Да се прикажат информациите за изведувачите, вклучувајќи ги нивните имиња, бројот на песни кои ги креирале и просечниот рејтинг на нивните песни:
     140  {{{#!sql
     141SELECT
     142    a.user_id,
     143    a.information AS artist_name,
     144    COUNT(s.song_id) AS total_songs,
     145    AVG(r.rating) AS average_rating
     146FROM ARTIST a
     147LEFT JOIN SONG s ON a.user_id = s.created_by
     148LEFT JOIN RATINGS r ON s.song_id = r.song_id
     149GROUP BY a.user_id, a.information
     150ORDER BY average_rating DESC;
     151  }}}
     152== Да се излистаат најдодаваните песни според регионот на корисниците,и да ги редоследува првите 10 најдодавани песни за секој регион. Песните според бројот на плејлисти во кои се наоѓаат, во опаѓачки редослед.
     153  {{{#!sql
     154SELECT s.song_title, COUNT(*) AS total_playlists, u.user_region
     155FROM SONG s
     156JOIN PLAYLIST_SONG ps ON s.song_id = ps.song_id
     157JOIN PLAYLIST p ON ps.playlist_id = p.playlist_id
     158JOIN LISTENER l ON p.created_by = l.user_id
     159JOIN USER u ON l.user_id = u.user_id
     160GROUP BY s.song_id, s.song_title, u.user_region
     161ORDER BY u.user_region, total_playlists DESC
     162LIMIT 10;
     163  }}}