SQL query to calculate average and insert into a table

Question

I'm working on a song archive database and I'm stuck on some queries. I would like to -

• Calculate the rating of each user by their average Comments value of score and inserting the rating into Users
• Calculating how much Purchases each user has
• Calculate the average score of a Song from the Comments table
• Calculating how many credits each user has spent on their purchases

Below you can find my tables...

CREATE TABLE Users
(
    username   NVARCHAR( 30 )   NOT NULL   PRIMARY KEY,
    pass       NVARCHAR( 16 ),
    email      NVARCHAR( 50 ),
    city       NVARCHAR( 10 ),
    credits    INT,
    rating     INT
)

CREATE TABLE Songs
(
    song_id       INT              NOT NULL   IDENTITY ( 1, 1 )   PRIMARY KEY,
    song_name     NVARCHAR( 30 ),
    username      NVARCHAR( 30 ),
    genre         INT,
    price         INT,
    song_length   INT,
    listens       INT
)

CREATE TABLE Genres
(
    genre_id     INT              NOT NULL   IDENTITY ( 1, 1 )   PRIMARY KEY,
    genre_name   NVARCHAR( 16 )
)


CREATE TABLE Purchases
(
    purchase_id      INT              NOT NULL   IDENTITY ( 1, 1 )   PRIMARY KEY,
    song_id          INT,
    username         NVARCHAR( 30 )
    date_purchased   DATETIME
)

CREATE TABLE Comments
(
    comment_id   INT   NOT NULL   IDENTITY ( 1, 1 )   PRIMARY KEY,
    username     NVARCHAR( 30 ),
    song_id      INT,
    text         NVARCHAR( 30 ),
    score        INT
)

Answer 1

I answered some of your questions. In addition to the respective queries I arranged them as common table expressions, which I think could be a convenient way to use them...

Calculating how much credits each user has spent on his purchases, might require to know your logic about how users invest their credits.

    WITH CTE_PurchasesByUser AS
    (
        SELECT p.username as username,  count(*) as NrOfPurchases
        FROM Purchases p
        GROUP BY p.username
    ),
    CTE_AverageScoreBySong AS
    (
        SELECT c.song_id as song_id, (sum(c.score)/count(c.score)) as AverageScore
        FROM Comments c
        GROUP BY c.song_id
    ),
    CTE_AverageScoreByUser AS
    (
        SELECT u.username as username, (sum(c.score)/count(c.score)) as AverageScore
        FROM Users u
        INNER JOIN Comments c ON u.username = c.username
        GROUP BY u.username
    )
    SELECT u.*, ISNULL(bbu.NrOfPurchases,0), asu.AverageScore 
    FROM Users u
    LEFT JOIN CTE_PurchasesByUser bbu ON u.username = bbu.username
    LEFT JOIN CTE_AverageScoreByUser asu ON u.username = asu.username

This SQL ran with your tables, yet I didn't test it with data rows...