Query problem to filter different fields in the same SQL table

Question

I have a table with GSUITE usage logs and I would like to filter some metrics one of them is the one that follows and that I am having problems to accomplish. (I’m a beginner in SQL)

I am looking for the following information: Total of distinct users who have sent at least one email at month intervals in one-year usage data + Total of users who have created at least one document type in the drive for the same one-month time intervals for the same database.

I tried the following, but as I need to Join with other tables and add another Join the query does not work and the timeout

    count(distinct u.user_profile_id) as user_google, 
    count(distinct u2.user_profile_id) as user_drive,

    EXTRACT(MONTH FROM u.date) as mes, EXTRACT(YEAR FROM u.date) as ano 
    from stinsights.user_usage as u 

     join stinsights.user_usage as u2  
    on u2.customer_id = u.customer_id 
    and EXTRACT(MONTH FROM u2.date) = EXTRACT(MONTH FROM u.date)
    and EXTRACT(YEAR FROM u2.date) = EXTRACT(YEAR FROM u.date)
    and (u2.drive_num_google_documents_created > 0 
    or u2.drive_num_google_spreadsheets_created > 0 
    or u2.drive_num_google_presentations_created > 0
    or u2.drive_num_google_forms_created > 0)


    where  u.customer_id = 'id_do_cliente' 
    and  EXTRACT(MONTH FROM u.gmail_last_interaction_time) = EXTRACT(MONTH FROM u.date)
    group by mes, ano order by ano, mes;```

Answer 1

I noticed some errors in your code:

from stinsights.user_usage as u 

 join stinsights.user_usage as u2  o código aqui

You are doing a stinsights.user_usage table and then you are relating to yourself in Join (I would make it very clear Inner Join), if you try to cross some more table will probably give redundant data and may even give the timeout.

Another good practice is to use the table conditions in Where and Where Ands, and not in Inner Join, use only what relates one table to another in Inner Join. Out that a select was missing at the beginning of your query.

Example:

SELECT 
count(distinct u.user_profile_id) as user_google, 
count(distinct u2.user_profile_id) as user_drive,
EXTRACT(MONTH FROM u.date) as mes, EXTRACT(YEAR FROM u.date) as ano 

from stinsights.user_usage as u 

where  u.customer_id = 'id_do_cliente' 
and (u2.drive_num_google_documents_created > 0 
     or u2.drive_num_google_spreadsheets_created > 0 
     or u2.drive_num_google_presentations_created > 0
     or u2.drive_num_google_forms_created > 0)
and EXTRACT(MONTH FROM u.gmail_last_interaction_time) = EXTRACT(MONTH FROM u.date)

group by mes, ano 

order by ano, mes;

Deletes what was unnecessary in your code.