CODEWITHSUNDEEP
sqlpostgresql
user2799827
user2799827

Reputation: 1127

Trying to query postgresql for current and longest streak

I have an Express API with a Postgres db and using Knex/ObjectionJS/Raw SQL for queries.

In the DB, I have an Acts table s.t. a User can create 1 Act per day. Here is the schema:

t.increments('id').primary()
t.string('deed')
t.integer('users_id').references('users.id').onDelete('CASCADE')

t.datetime("created_at");
t.datetime("updated_at");

My goal is to create 2 queries to return (1) the current streak of the number of consecutive days on which a User creates and Act and (2) the User's longest streak of consecutive days with an Act created.

I have seen various example queries that purport to do this, but have not been able to get them to work for me.

Updating code in line with @Jonathan's suggestion.

Here is my route:

// return longest streaks of daily acts by user
router.get('/longest_streak', (req, res, next) => {
  let results = knex.raw(`
  WITH RECURSIVE cte 
  AS
  (
    SELECT users_id, created_at, 1 AS cnt
    FROM acts a
    UNION ALL
    SELECT acts.users_id, acts.created_at, c.cnt + 1
    FROM acts a INNER JOIN cte c
    ON acts.users_id = c.users_id
    AND acts.created_at = c.created_at - interval '1' day
  )
  SELECT users_id, MAX(cnt) AS most_consecutive
  FROM cte
  GROUP BY users_id;
  `);
  console.log('getting current streak');
  res.json(results);
})

Here is the trace:

Running on localhost:8000
getting current streak
Cannot find module 'pg-native'
TypeError: Converting circular structure to JSON
    at JSON.stringify (<anonymous>)
    at stringify (/Users/dariusgoore/development/writerboard/writerboard-express-api/node_modules/express/lib/response.js:1123:12)
    at ServerResponse.json (/Users/dariusgoore/development/writerboard/writerboard-express-api/node_modules/express/lib/response.js:260:14)
    at router.get (/Users/dariusgoore/development/writerboard/writerboard-express-api/src/routes/acts.js:103:7)
    at Layer.handle [as handle_request] (/Users/dariusgoore/development/writerboard/writerboard-express-api/node_modules/express/lib/router/layer.js:95:5)
    at next (/Users/dariusgoore/development/writerboard/writerboard-express-api/node_modules/express/lib/router/route.js:137:13)
    at Route.dispatch (/Users/dariusgoore/development/writerboard/writerboard-express-api/node_modules/express/lib/router/route.js:112:3)
    at Layer.handle [as handle_request] (/Users/dariusgoore/development/writerboard/writerboard-express-api/node_modules/express/lib/router/layer.js:95:5)
    at /Users/dariusgoore/development/writerboard/writerboard-express-api/node_modules/express/lib/router/index.js:281:22
    at Function.process_params (/Users/dariusgoore/development/writerboard/writerboard-express-api/node_modules/express/lib/router/index.js:335:12)
    at next (/Users/dariusgoore/development/writerboard/writerboard-express-api/node_modules/express/lib/router/index.js:275:10)
    at Function.handle (/Users/dariusgoore/development/writerboard/writerboard-express-api/node_modules/express/lib/router/index.js:174:3)
    at router (/Users/dariusgoore/development/writerboard/writerboard-express-api/node_modules/express/lib/router/index.js:47:12)
    at Layer.handle [as handle_request] (/Users/dariusgoore/development/writerboard/writerboard-express-api/node_modules/express/lib/router/layer.js:95:5)
    at trim_prefix (/Users/dariusgoore/development/writerboard/writerboard-express-api/node_modules/express/lib/router/index.js:317:13)
    at /Users/dariusgoore/development/writerboard/writerboard-express-api/node_modules/express/lib/router/index.js:284:7

Upvotes: 3

Views: 1247

Answers (1)

Jonathan Willcock
Jonathan Willcock

Reputation: 5255

You can do this with a recursive cte, something along the lines of:

WITH RECURSIVE cte
AS
(
    select users_id, created_at, 1 as cnt
    FROM acts 
    UNION ALL
    select a.users_id, a.created_at, c.cnt + 1
    FROM acts a inner JOIN cte c
    ON a.users_id = c.users_id
    AND a.created_at = c.created_at - interval '1' day
)
SELECT users_id, MAX(cnt) AS most_consecutive
FROM cte
GROUP BY users_id;

To get the current streak (by current I presume you mean ending on a given date - today or possibly yesterday?), then all you need to do is to add a where clause to the first part of the cte to restrict the first date chosen to today or yesterday.

In case by most consecutive you also need to know when each streak was, then you will need to add a second cte, so the query becomes:

WITH RECURSIVE cte
AS
(
    select users_id, created_at, 1 as cnt
    FROM acts 
    UNION ALL
    select a.users_id, a.created_at, c.cnt + 1
    FROM acts a inner JOIN cte c
    ON a.users_id = c.users_id
    AND a.created_at = c.created_at - interval '1' day
),
ctemax AS
(SELECT users_id, MAX(cnt) as most_consecutive 
FROM cte
GROUP BY users_id)
SELECT c.users_id, c.created_at, m.most_consecutive
FROM cte c 
INNER JOIN ctemax m ON c.users_id = m.users_id AND c.cnt = m.most_consecutive;

This gives you both the maximum streak and the day on which that streak started.

UPDATE

One thing I forgot to mention: for this to work, you will need to cast created_at to date!

Upvotes: 2

Related Questions