Google BigQuery: SUM returns different results even on SUM alias changes

Question

I have the following query:

SELECT 
      p_id,
      r_date,
      
      SUM(CASE WHEN p_type='h' AND post_p='c' THEN 1 ELSE 0 END) as test1
      
      FROM (
      
      SELECT 152234223 AS p_id,
      date AS r_date,
      sequence as p_type,
      
      LEAD(p_type, 1) OVER 
      (PARTITION BY u_id ORDER BY visit_id) AS post_p
      
      FROM   (Table_date_range([152234223.ses_],Timestamp('25022016'),Timestamp('29022016'))) 
 
      GROUP BY 1,2

This query would returns the result of SUM, for example 145. But if I change the alias of the sum become "testing" or "test1234567890" it would returns a different value, either higher or lower than 145.

I am wondering if someone can explain me about this. Need a solutions!

Many thanks

Answer 1

If you can share your data-set it will be easier to validate this... but just from reading your code:

Your problem seems to be here:

LEAD(p_type, 1) OVER 
      (PARTITION BY u_id ORDER BY u_id) AS post_p

As you partition by and order by the same attribute, the order of the records within each partition can be inconsistent. So each time you run the query, LEAD() might return different results.

The reason it seems as change in aliases is probably related to return of cached results.