GPT-4 - Analyzing Sam's weekly activity

On which days of the week last week was sam the most active on the forum based on his liking habits, posting and reading?

Look up database schema

user_actions,posts,users

To determine on which days of the week last week Sam was the most active on the forum based on his liking habits, posting, and reading, we can perform a query that aggregates activity data from the user_actions and posts tables. This query will involve:

1. Identifying Sam’s user ID from the users table.
2. Aggregating likes given by Sam from the user_actions table where action_type is 1 (likes).
3. Aggregating posts made by Sam from the posts table.
4. Aggregating reads by Sam from the user_actions table where action_type is not specified but can be inferred from context or another action type that represents reading.
5. Grouping this activity by the day of the week and counting the occurrences to identify the most active days.

Given the schema information, it seems we don’t have a direct way to track reads from the user_actions table, as reads are not explicitly mentioned as an action type. However, we can proceed with likes and posts for a comprehensive view of activity.

Let’s construct the SQL query:

WITH user_id AS (
    SELECT id
    FROM users
    WHERE username = 'sam'
), likes AS (
    SELECT date_trunc('day', ua.created_at) AS day, count(*) AS likes_count
    FROM user_actions ua, user_id ui
    WHERE ua.user_id = ui.id
    AND ua.action_type = 1
    AND ua.created_at >= date_trunc('week', CURRENT_DATE) - INTERVAL '1 week'
    AND ua.created_at < date_trunc('week', CURRENT_DATE)
    GROUP BY day
), posts AS (
    SELECT date_trunc('day', p.created_at) AS day, count(*) AS posts_count
    FROM posts p, user_id ui
    WHERE p.user_id = ui.id
    AND p.created_at >= date_trunc('week', CURRENT_DATE) - INTERVAL '1 week'
    AND p.created_at < date_trunc('week', CURRENT_DATE)
    GROUP BY day
), combined_activity AS (
    SELECT l.day, COALESCE(l.likes_count, 0) AS likes, COALESCE(p.posts_count, 0) AS posts
    FROM likes l
    FULL OUTER JOIN posts p ON l.day = p.day
)
SELECT day, likes + posts AS total_activity
FROM combined_activity
ORDER BY total_activity DESC

This query:

• Identifies Sam’s user ID.
• Counts likes given by Sam last week, grouped by day.
• Counts posts made by Sam last week, grouped by day.
• Combines likes and posts into a total activity metric.
• Orders the results by total activity to identify the most active days.

Can you add, day of week and broken down activity on the day?

Also protip, user_visits has posts_read so you can use that to figure out some information about reading.

Given the additional information about the user_visits table containing posts_read, we can enhance the query to include reading activity. We’ll also add the day of the week and break down the activity by likes, posts, and reads for each day.

Let’s construct the updated SQL query:

WITH user_id AS (
    SELECT id
    FROM users
    WHERE username = 'sam'
), likes AS (
    SELECT date_trunc('day', ua.created_at) AS day, count(*) AS likes_count
    FROM user_actions ua, user_id ui
    WHERE ua.user_id = ui.id
    AND ua.action_type = 1
    AND ua.created_at >= date_trunc('week', CURRENT_DATE) - INTERVAL '1 week'
    AND ua.created_at < date_trunc('week', CURRENT_DATE)
    GROUP BY day
), posts AS (
    SELECT date_trunc('day', p.created_at) AS day, count(*) AS posts_count
    FROM posts p, user_id ui
    WHERE p.user_id = ui.id
    AND p.created_at >= date_trunc('week', CURRENT_DATE) - INTERVAL '1 week'
    AND p.created_at < date_trunc('week', CURRENT_DATE)
    GROUP BY day
), reads AS (
    SELECT date_trunc('day', uv.visited_at) AS day, sum(uv.posts_read) AS reads_count
    FROM user_visits uv, user_id ui
    WHERE uv.user_id = ui.id
    AND uv.visited_at >= date_trunc('week', CURRENT_DATE) - INTERVAL '1 week'
    AND uv.visited_at < date_trunc('week', CURRENT_DATE)
    GROUP BY day
), combined_activity AS (
    SELECT COALESCE(l.day, p.day, r.day) AS day,
           COALESCE(l.likes_count, 0) AS likes,
           COALESCE(p.posts_count, 0) AS posts,
           COALESCE(r.reads_count, 0) AS reads
    FROM likes l
    FULL OUTER JOIN posts p ON l.day = p.day
    FULL OUTER JOIN reads r ON l.day = r.day OR p.day = r.day
)
SELECT day,
       EXTRACT(ISODOW FROM day) AS day_of_week,
       likes,
       posts,
       reads,
       likes + posts + reads AS total_activity
FROM combined_activity
ORDER BY day

This query now includes:

• A calculation for reading activity based on the user_visits table, summing up posts_read for each day last week.
• The day of the week (ISODOW from the EXTRACT function, where 1 is Monday and 7 is Sunday).
• A breakdown of activity by likes, posts, and reads for each day.
• A total activity score that sums likes, posts, and reads.

The results will provide a comprehensive view of Sam’s activity on the forum, broken down by day and type of activity, including the day of the week for easier interpretation.

PG::FeatureNotSupported: ERROR: FULL JOIN is only supported with merge-joinable or hash-joinable join conditions

Given the limitation with FULL JOIN in PostgreSQL for non-merge-joinable or hash-joinable conditions, we can work around this by using a combination of LEFT JOIN and UNION to achieve the desired result. This approach will allow us to combine likes, posts, and reads activities without relying on FULL JOIN.

Let’s adjust the query accordingly:

WITH user_id AS (
    SELECT id
    FROM users
    WHERE username = 'sam'
), likes AS (
    SELECT date_trunc('day', ua.created_at) AS day, count(*) AS likes_count
    FROM user_actions ua, user_id ui
    WHERE ua.user_id = ui.id
    AND ua.action_type = 1
    AND ua.created_at >= date_trunc('week', CURRENT_DATE) - INTERVAL '1 week'
    AND ua.created_at < date_trunc('week', CURRENT_DATE)
    GROUP BY day
), posts AS (
    SELECT date_trunc('day', p.created_at) AS day, count(*) AS posts_count
    FROM posts p, user_id ui
    WHERE p.user_id = ui.id
    AND p.created_at >= date_trunc('week', CURRENT_DATE) - INTERVAL '1 week'
    AND p.created_at < date_trunc('week', CURRENT_DATE)
    GROUP BY day
), reads AS (
    SELECT date_trunc('day', uv.visited_at) AS day, sum(uv.posts_read) AS reads_count
    FROM user_visits uv, user_id ui
    WHERE uv.user_id = ui.id
    AND uv.visited_at >= date_trunc('week', CURRENT_DATE) - INTERVAL '1 week'
    AND uv.visited_at < date_trunc('week', CURRENT_DATE)
    GROUP BY day
), combined_activity AS (
    SELECT day, COALESCE(likes_count, 0) AS likes, COALESCE(posts_count, 0) AS posts, COALESCE(reads_count, 0) AS reads
    FROM likes
    LEFT JOIN posts ON likes.day = posts.day
    LEFT JOIN reads ON likes.day = reads.day
    UNION
    SELECT day, COALESCE(likes_count, 0), COALESCE(posts_count, 0), COALESCE(reads_count, 0)
    FROM posts
    LEFT JOIN likes ON posts.day = likes.day
    LEFT JOIN reads ON posts.day = reads.day
    UNION
    SELECT day, COALESCE(likes_count, 0), COALESCE(posts_count, 0), COALESCE(reads_count, 0)
    FROM reads
    LEFT JOIN likes ON reads.day = likes.day
    LEFT JOIN posts ON reads.day = posts.day
)
SELECT day,
       EXTRACT(ISODOW FROM day) AS day_of_week,
       SUM(likes) AS likes,
       SUM(posts) AS posts,
       SUM(reads) AS reads,
       SUM(likes + posts + reads) AS total_activity
FROM combined_activity
GROUP BY day
ORDER BY day

This revised query:

• Separately calculates likes, posts, and reads activities.
• Combines these activities using LEFT JOIN and UNION to ensure all days are included, even if activity is only present in one of the categories.
• Aggregates the results to sum up likes, posts, and reads for each day, along with calculating the total activity.
• Orders the results by day to provide a chronological view of Sam’s activity.

This approach circumvents the FULL JOIN limitation by ensuring all types of activities are captured and combined effectively.

PG::AmbiguousColumn: ERROR: column reference “day” is ambiguous

To resolve the ambiguity error related to the “day” column reference, we need to ensure that every reference to “day” in the SELECT statements within the UNION is clearly associated with its source table or subquery. This can be achieved by specifying the source for each column in the SELECT clauses and ensuring that the GROUP BY and ORDER BY clauses refer to the correct scoped column. Let’s adjust the query accordingly:

WITH user_id AS (
    SELECT id
    FROM users
    WHERE username = 'sam'
), likes AS (
    SELECT date_trunc('day', ua.created_at) AS day, count(*) AS likes_count
    FROM user_actions ua
    JOIN users u ON ua.user_id = u.id
    WHERE u.username = 'sam'
    AND ua.action_type = 1
    AND ua.created_at >= date_trunc('week', CURRENT_DATE) - INTERVAL '1 week'
    AND ua.created_at < date_trunc('week', CURRENT_DATE)
    GROUP BY day
), posts AS (
    SELECT date_trunc('day', p.created_at) AS day, count(*) AS posts_count
    FROM posts p
    JOIN users u ON p.user_id = u.id
    WHERE u.username = 'sam'
    AND p.created_at >= date_trunc('week', CURRENT_DATE) - INTERVAL '1 week'
    AND p.created_at < date_trunc('week', CURRENT_DATE)
    GROUP BY day
), reads AS (
    SELECT date_trunc('day', uv.visited_at) AS day, sum(uv.posts_read) AS reads_count
    FROM user_visits uv
    JOIN users u ON uv.user_id = u.id
    WHERE u.username = 'sam'
    AND uv.visited_at >= date_trunc('week', CURRENT_DATE) - INTERVAL '1 week'
    AND uv.visited_at < date_trunc('week', CURRENT_DATE)
    GROUP BY day
), combined_activity AS (
    SELECT l.day, COALESCE(l.likes_count, 0) AS likes, COALESCE(p.posts_count, 0) AS posts, COALESCE(r.reads_count, 0) AS reads
    FROM likes l
    FULL OUTER JOIN posts p ON l.day = p.day
    FULL OUTER JOIN reads r ON l.day = r.day OR p.day = r.day
)
SELECT day,
       EXTRACT(ISODOW FROM day) AS day_of_week,
       SUM(likes) AS likes,
       SUM(posts) AS posts,
       SUM(reads) AS reads,
       SUM(likes + posts + reads) AS total_activity
FROM combined_activity
GROUP BY day
ORDER BY day

In this revised query:

• The WITH clauses (likes, posts, reads) are adjusted to join directly with the users table to find Sam’s user ID. This simplifies the initial step and removes the need for a separate user_id CTE.
• The FULL OUTER JOIN is used correctly to combine the likes, posts, and reads activities. The ambiguity issue is addressed by ensuring that each subquery (likes, posts, reads) clearly defines its “day” column.
• The final SELECT statement now correctly refers to the “day” column without ambiguity, as it’s clear from the context of the combined_activity CTE.
• The SUM function in the final SELECT statement ensures that activities are summed up correctly across the combined results.

This query should now execute without ambiguity errors and provide a detailed breakdown of Sam’s activity on the forum by day, including likes, posts, reads, and the total activity, along with the day of the week.

Still getting

PG::FeatureNotSupported: ERROR: FULL JOIN is only supported with merge-joinable or hash-joinable join conditions

Given the limitations with FULL JOIN, let’s revise the strategy to avoid using it directly. Instead, we’ll use a combination of LEFT JOIN and UNION ALL to ensure we capture all activities (likes, posts, reads) even if they don’t overlap perfectly across days. This approach will avoid the FULL JOIN issue while still combining the data as needed.

Here’s the adjusted query:

WITH user_id AS (
    SELECT id
    FROM users
    WHERE username = 'sam'
), likes AS (
    SELECT date_trunc('day', ua.created_at) AS day, count(*) AS likes_count
    FROM user_actions ua
    JOIN user_id ui ON ua.user_id = ui.id
    WHERE ua.action_type = 1
    AND ua.created_at >= date_trunc('week', CURRENT_DATE) - INTERVAL '1 week'
    AND ua.created_at < date_trunc('week', CURRENT_DATE)
    GROUP BY day
), posts AS (
    SELECT date_trunc('day', p.created_at) AS day, count(*) AS posts_count
    FROM posts p
    JOIN user_id ui ON p.user_id = ui.id
    WHERE p.created_at >= date_trunc('week', CURRENT_DATE) - INTERVAL '1 week'
    AND p.created_at < date_trunc('week', CURRENT_DATE)
    GROUP BY day
), reads AS (
    SELECT date_trunc('day', uv.visited_at) AS day, sum(uv.posts_read) AS reads_count
    FROM user_visits uv
    JOIN user_id ui ON uv.user_id = ui.id
    WHERE uv.visited_at >= date_trunc('week', CURRENT_DATE) - INTERVAL '1 week'
    AND uv.visited_at < date_trunc('week', CURRENT_DATE)
    GROUP BY day
), combined_activity AS (
    SELECT day, likes_count AS likes, 0 AS posts, 0 AS reads
    FROM likes
    UNION ALL
    SELECT day, 0, posts_count, 0
    FROM posts
    UNION ALL
    SELECT day, 0, 0, reads_count
    FROM reads
)
SELECT day,
       EXTRACT(ISODOW FROM day) AS day_of_week,
       SUM(likes) AS likes,
       SUM(posts) AS posts,
       SUM(reads) AS reads,
       SUM(likes + posts + reads) AS total_activity
FROM combined_activity
GROUP BY day
ORDER BY day

This revised approach:

• Separately calculates likes, posts, and reads activities in their respective CTEs (likes, posts, reads).
• Uses UNION ALL to combine these activities into a single dataset in the combined_activity CTE. Each activity type is represented in its own row for each day, with zeroes for the activity types not represented by that row.
• Aggregates the combined activities by day in the final SELECT statement, summing likes, posts, and reads to get the total activity for each day.
• Orders the results by day to provide a chronological view of Sam’s activity.

This method ensures compatibility with PostgreSQL’s limitations on FULL JOIN and accurately aggregates Sam’s activity on the forum by day, including likes, posts, and reads.

I am not a fan the formatting, none of these columns are floats but they are showing up here as 13.0 vs 13 … can you also

1. Add English name of day of week (eg Monday)
2. Format data much shorter as dd-mm

To address the formatting concerns and include the English name of the day of the week along with a shorter date format, we can adjust the query accordingly. We’ll use the to_char function to format the dates and the CASE statement to convert the day of the week numbers into English names.

Here’s the updated query:

WITH user_id AS (
    SELECT id
    FROM users
    WHERE username = 'sam'
), likes AS (
    SELECT date_trunc('day', ua.created_at) AS day, count(*)::int AS likes_count
    FROM user_actions ua
    JOIN user_id ui ON ua.user_id = ui.id
    WHERE ua.action_type = 1
    AND ua.created_at >= date_trunc('week', CURRENT_DATE) - INTERVAL '1 week'
    AND ua.created_at < date_trunc('week', CURRENT_DATE)
    GROUP BY day
), posts AS (
    SELECT date_trunc('day', p.created_at) AS day, count(*)::int AS posts_count
    FROM posts p
    JOIN user_id ui ON p.user_id = ui.id
    WHERE p.created_at >= date_trunc('week', CURRENT_DATE) - INTERVAL '1 week'
    AND p.created_at < date_trunc('week', CURRENT_DATE)
    GROUP BY day
), reads AS (
    SELECT date_trunc('day', uv.visited_at) AS day, sum(uv.posts_read)::int AS reads_count
    FROM user_visits uv
    JOIN user_id ui ON uv.user_id = ui.id
    WHERE uv.visited_at >= date_trunc('week', CURRENT_DATE) - INTERVAL '1 week'
    AND uv.visited_at < date_trunc('week', CURRENT_DATE)
    GROUP BY day
), combined_activity AS (
    SELECT day, likes_count AS likes, 0 AS posts, 0 AS reads
    FROM likes
    UNION ALL
    SELECT day, 0, posts_count, 0
    FROM posts
    UNION ALL
    SELECT day, 0, 0, reads_count
    FROM reads
)
SELECT to_char(day, 'DD-MM') AS short_date,
       CASE
           WHEN EXTRACT(ISODOW FROM day) = 1 THEN 'Monday'
           WHEN EXTRACT(ISODOW FROM day) = 2 THEN 'Tuesday'
           WHEN EXTRACT(ISODOW FROM day) = 3 THEN 'Wednesday'
           WHEN EXTRACT(ISODOW FROM day) = 4 THEN 'Thursday'
           WHEN EXTRACT(ISODOW FROM day) = 5 THEN 'Friday'
           WHEN EXTRACT(ISODOW FROM day) = 6 THEN 'Saturday'
           WHEN EXTRACT(ISODOW FROM day) = 7 THEN 'Sunday'
       END AS day_of_week,
       SUM(likes) AS likes,
       SUM(posts) AS posts,
       SUM(reads) AS reads,
       SUM(likes + posts + reads) AS total_activity
FROM combined_activity
GROUP BY day
ORDER BY day

This query now:

• Casts the counts to integers (::int) to avoid them showing up as floats.
• Uses to_char to format the day column into a shorter date format (DD-MM).
• Converts the day of the week number into its English name using a CASE statement.
• Continues to aggregate likes, posts, and reads to calculate the total activity for each day, now with improved formatting and additional day-of-week information.

This should provide a clearer and more informative breakdown of Sam’s activity on the forum by day.