Complex SQL syntax

Question

I have a game, and in the database I'm saving the user actions by date & time.

CREATE TABLE user_actions
(
    aId BIGSERIAL PRIMARY KEY NOT NULL,
    userId BIGINT NOT NULL REFERENCES users(uId) DEFERRABLE INITIALLY DEFERRED,
    aDate TIMESTAMP without time zone DEFAULT now(),
    aType INTEGER NOT NULL DEFAULT 0
);

My users are identified with email

CREATE TABLE users(
   uId BIGSERIAL PRIMARY KEY NOT NULL,
   uName VARCHAR (50) NOT NULL,
   uEmail VARCHAR (75) UNIQUE  NULL
);

and each day new prizes are added each day has a different number of prizes

CREATE TABLE prizes(
   pId BIGSERIAL PRIMARY KEY NOT NULL,
   pDate TIMESTAMP without time zone DEFAULT now(),
   pType INTEGER NULL
   pSize INTEGER NULL
);

This query list the userId and his last action date, per user

select distinct userId, max(aDate) from user_actions GROUP BY userId order by userId;

I want to create a query that will count the number of prizes added since each user last action.

I'm running:

OS: Debian
DB: Postgresql
code: Django

Answer 1

I think I will use CTE though It has not been tested

WITH last_actions AS (
SELECT DISTINCT userId, MAX(aDate) as last_logged 
 FROM user_actions 
 GROUP BY userId ORDER BY userId)

SELECT a.userId, COUNT(b.pDate) 
 FROM last_actions a, prizes b 
 WHERE b.pDate >= a.last_logged      
 GROUP BY a.userId;