Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Clear out junk files and repair common Windows errors3Scan for outdated or missing drivers - takes under a minuteTo find users with at least three purchases in each of April, May, and June 2023, first group purchases by user and month, then group the qualifying months by user. Finally, total each selected user’s purchases across the full three-month period. The query below uses PostgreSQL syntax and includes purchases with a NULL amount in the monthly counts.
The PostgreSQL query
WITH monthly_counts AS (
SELECT
user_id,
date_trunc('month', purchase_date)::date AS purchase_month,
COUNT(*) AS purchase_count
FROM purchases
WHERE purchase_date >= DATE '2023-04-01'
AND purchase_date < DATE '2023-07-01'
GROUP BY user_id, date_trunc('month', purchase_date)::date
HAVING COUNT(*) >= 3
), power_users AS (
SELECT user_id
FROM monthly_counts
GROUP BY user_id
HAVING COUNT(*) = 3
)
SELECT
u.user_id,
u.email,
CAST(COALESCE(SUM(p.amount), 0) AS DECIMAL(10, 2)) AS total_amount_spent
FROM power_users pu
JOIN users u ON u.user_id = pu.user_id
JOIN purchases p ON p.user_id = pu.user_id
WHERE p.purchase_date >= DATE '2023-04-01'
AND p.purchase_date < DATE '2023-07-01'
GROUP BY u.user_id, u.email
ORDER BY total_amount_spent DESC, u.user_id ASC;This assumes users.user_id identifies one user row and that the purchase date and ID columns have compatible types. PostgreSQL requires selected values in a grouped query to be grouped or aggregated; here the user ID and email are grouping keys, and the purchase amounts are summed.
How the two GROUP BY stages find qualifying users
First, find qualifying user-months
The first GROUP BY creates one result row per user_id and calendar month. WHERE limits the rows before this aggregation, while HAVING COUNT(*) >= 3 keeps only user-month groups containing three or more purchase rows. PostgreSQL documents this order of filtering and grouping in its table-expression documentation.
Use COUNT(*), not COUNT(amount), because the requirement counts purchases even when an amount is NULL. PostgreSQL’s aggregate documentation distinguishes counting all input rows from counting non-NULL values of an expression.
#1 Best Overall
Then, require all three months
The second GROUP BY collapses the qualifying monthly rows to one row per user. Since the date filter covers exactly April, May, and June 2023 and the first grouping can produce at most one row per user per month, HAVING COUNT(*) = 3 means the user qualified in every target month. If a month has fewer than three purchases—or none at all—it contributes no qualifying row, so the user cannot reach three.
Why the final sum uses the original purchases
The monthly counts decide who qualifies; they are not the amounts to total. The final join returns to purchases and sums every purchase for each selected user within the three-month window, including purchases beyond the three-per-month minimum. PostgreSQL’s SUM ignores NULL amounts; if every amount in a selected user’s period is NULL, it returns NULL. COALESCE(..., 0) applies a zero-total convention for that case. See the PostgreSQL aggregate-function documentation.
The cast formats the result as DECIMAL(10, 2), matching the requested two-decimal output. Confirm the numeric type and rounding behavior if adapting the query to a different database.
Date boundaries and common pitfalls
Use a half-open date range for timestamps
The predicate includes dates from April 1 onward and stops immediately before July 1. For timestamp values, this includes every time on June 30. An inclusive upper bound of June 30 at midnight can exclude later timestamps that same day. For a DATE column, an inclusive end date of June 30 can also work, but the half-open range remains explicit.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Keep the year in the month grouping
Do not group only by month number if the data may span multiple years: April in one year would be combined with April in another. The query truncates each date to its year-and-month boundary, so each calendar month is distinct.
Be careful if the interval changes
The condition HAVING COUNT(*) = 3 is correct here because the filter contains exactly three target months. If you change the interval or require a different set of periods, derive the expected number of months from the requirement or check each required month explicitly.
Rank #4
Prevent duplicate user rows from inflating totals
The example assumes one row per user_id in users. Duplicate user records would duplicate joined purchase rows and could inflate the sum. Enforce uniqueness on the user ID or aggregate purchases before joining to a non-unique user table.
Adapting the date expression to another SQL dialect
The query is PostgreSQL-compatible, not universal SQL. The source problem mentions EXTRACT(MONTH ...) for PostgreSQL, MySQL, and DuckDB, and MONTH(...) for SQL Server, but those portability notes are not independently verified here. Check the target engine’s syntax for truncating or extracting a year-month value, date literals, casts, and timestamp boundaries before using a translated query.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteQuick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
