marpe
marpe
Explore posts from servers
CCConvex CommunityDTDrizzle Team
PostsComments
DTDrizzle Team
Created by marpe on 2/13/2025 in #help
Cant figure out how to converting a sql query to drizzle syntax
I've been trying to figure out how to convert this query for almost two hours, I don't understand how these multiple with statements translates into the drizzle syntax, anyone knows? 
WITH review_dates AS (
      SELECT DISTINCT DATE(${ReviewLog.createdAt}) AS review_date
      FROM ${ReviewLog}
    ),
    streaks AS (
      SELECT review_date,
             review_date - CAST(ROW_NUMBER() OVER (ORDER BY review_date) AS integer) AS grp
      FROM review_dates
    ),
    current_streak AS (
      SELECT COUNT(*)::integer AS consecutive_days
      FROM streaks
      WHERE grp = (SELECT grp FROM streaks ORDER BY review_date DESC LIMIT 1)
    )
    SELECT
      (SELECT consecutive_days FROM current_streak) AS consecutive_days,
      CASE
        WHEN (SELECT MAX(review_date) FROM review_dates) = CURRENT_DATE THEN TRUE
        ELSE FALSE
      END AS streak_ongoing,
      (SELECT COUNT(*)::integer FROM ${ReviewLog}) AS total_reviews,
      (SELECT COUNT(*)::integer FROM review_dates) AS total_review_days,
      (CURRENT_DATE - (SELECT MAX(review_date) FROM review_dates))::integer AS days_since_last_review
WITH review_dates AS (
      SELECT DISTINCT DATE(${ReviewLog.createdAt}) AS review_date
      FROM ${ReviewLog}
    ),
    streaks AS (
      SELECT review_date,
             review_date - CAST(ROW_NUMBER() OVER (ORDER BY review_date) AS integer) AS grp
      FROM review_dates
    ),
    current_streak AS (
      SELECT COUNT(*)::integer AS consecutive_days
      FROM streaks
      WHERE grp = (SELECT grp FROM streaks ORDER BY review_date DESC LIMIT 1)
    )
    SELECT
      (SELECT consecutive_days FROM current_streak) AS consecutive_days,
      CASE
        WHEN (SELECT MAX(review_date) FROM review_dates) = CURRENT_DATE THEN TRUE
        ELSE FALSE
      END AS streak_ongoing,
      (SELECT COUNT(*)::integer FROM ${ReviewLog}) AS total_reviews,
      (SELECT COUNT(*)::integer FROM review_dates) AS total_review_days,
      (CURRENT_DATE - (SELECT MAX(review_date) FROM review_dates))::integer AS days_since_last_review
11 replies
CCConvex Community
Created by marpe on 6/15/2024 in #show-and-tell
Trivia game app
No description
6 replies