I am fairly New to SQL using BigQuery, and I’m looking for some assistance with making a single query from the pieces I’ve figured out so far.
The ask: Pull all case data from table email_cases where case_creation_time is within target date range and is_spam = False (no spam, spam bad)
AND
member_account is greater than 1 (same account has submitted more than 1 ticket in target time).
I’ve created two separate queries that each do a portion of this, but I know is should be simple to make them into a single query.
Query 1
Select member_account, case_id,
case_creation_time, handle_time,
Date_add(case_creation_time, Interval (handle_time) second) as Clsd
from `email_cases`
where case_creation_time between Timestamp(date_sub(current_date, interval 8 day))
and timestamp(date_add(current_date, interval -1 day))
and is_spam = False
This results in the cases during the time period in question, including creating a “Clsd” field that calculates the closed time by taking creation time and adding handle time.
Query 2
SELECT member_account, COUNT(member_account)
FROM `email_cases`
GROUP BY member_account
HAVING COUNT(member_account) > 1
ORDER BY member_account desc
This is what I threw together to identify customers that have more than 1 case associated with the member account. There are probably many ways to get that result, but this is what I’ve got so far.
Now the issue should be simple since both queries target the same table and even use the same column. I know this should be very basic for experienced users but my brain isn’t making the connection right now. Any assistance would be greatly appreciated.
Chip Brazell is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
You can combine the 2 queries by using a “derived table” like so:
SELECT
e.member_account
, e.case_id
, e.case_creation_time
, e.handle_time
, Date_add(e.case_creation_time, Interval(e.handle_time) second) AS Clsd
FROM `email_cases` e
INNER JOIN (
SELECT member_account
FROM `email_cases`
GROUP BY member_account
HAVING COUNT(member_account) > 1
) m ON e.member_account = m.member_account
WHERE e.case_creation_time BETWEEN TIMESTAMP (date_sub(CURRENT_DATE, interval 8 day))
AND TIMESTAMP (date_add(CURRENT_DATE, interval - 1 day))
AND e.is_spam = False
ORDER BY member_account DESC
Or, (as suggested by Fred above) use a QUALIFY clause (ref)
SELECT
e.member_account
, e.case_id
, e.case_creation_time
, e.handle_time
, Date_add(e.case_creation_time, Interval(e.handle_time) second) AS Clsd
FROM `email_cases` e
WHERE e.case_creation_time BETWEEN TIMESTAMP (date_sub(CURRENT_DATE, interval 8 day))
AND TIMESTAMP (date_add(CURRENT_DATE, interval - 1 day))
AND e.is_spam = False
QUALIFY COUNT(*) OVER (PARTITION BY e.member_account) > 1
ORDER BY member_account DESC
1