Nrthugu

I've the following 2 tables:

Table1 - raw data without revenue data:

datehour | geo | platform | dimension_4 | dimension_5

metric_1 | metric_2 | billable_actions (int)

Table2 - the revenue data, which is originated from another source
but eventually inserted into a table with the following schema
(please note that it has 2 missing dimensions):

datehour | geo | platform | revenue (float)

I need to merge Table2 into Table1 ,
so I can calculate the revenue only from table1.

THE QUERY

SELECT

 datehour,

 cc,

 platform,

 dimension_1,

 dimension_2,

 billable_actions,

 CASE

   WHEN revenue > 0 AND billable_actions > 0 THEN (revenue/total_billable_actions)*billable_actions

   WHEN metric_1 IS NULL AND metric_2 IS NULL THEN revenue

   ELSE 0

 END calc_revenue

FROM (

 SELECT

   IFNULL(e.datehour,d.from_ts) datehour,

   IFNULL(e.cc, d.cc) cc,

   IFNULL(e.platform, d.platform) platform,

   e.dimension1,

   e.dimension2,

   e.billable_actions,

   SUM(e.billable_actions) OVER (PARTITION BY e.datehour, e.platform, e.geo) total_billable_actions,

   d.revenue

 FROM Table1 e

 FULL JOIN Table2 d

 ON

  e.datehour = d.from_ts AND

  e.appkey = d.appkey AND

  e.cc = d.cc AND

  e.platform = d.platform

)

EXPECTED RESULT & THE PROBLEM

Both tables revenues should be equal after the join.
I'm testing:

select sum(revenue) from Table2 ==

select sum(row_revenue) from JoinedTable

but row_revenue is always lower:
Table2 sum = 0.44449199771042913
Joined table sum = 0.4421989977126941

Can some one please point me for the reason why they are not equal?
I've a working example at the end of the question .

NOTES

note 1 - the revenue is calculated the following way:

1) Table1 FULL JOIN Table2 ON:

T1.datehour = T2.datehour AND

T1.geo = T2.geo AND

T1.platform = T2.platform

*please read note 2,3 regard why I use full join.

2) on Table1 , sum the billable actions, partition by datehour, geo and platform.

SUM(billable_actions) OVER (PARTITION BY datehour, geo, platform)  

AS total_billable_actions

3) Now , we have a table , where each row contains the total_billable_actions per the group , the total revenue and the total actions for the row. wrapping it with another select and calculating the row revenue:

row_revenue = (revenue / total_billable_actions) * billable_actions

note 2
we may have rows from Table1 which do not match any row from table2,
but we still need the data found in those rows . those rows will not affect the revenue calculations.

note 3
we may have rows from Table2 which also do not fit the join.
we need those rows , stay untouched , with the same revenue.

note 4 regard floating point issues:
BigQuery uses 8byte double precision for float,
and I've multiplied all results with great numbers (1000, 1000000) to find out if the issue is decimal point precision.
It seems that multiplying the result gives the same exact result.

working example

for simplicity I've taken out one dimension and the other metrics.

Left table:

datehour   geo   platform   dimension_1   billable_actions

15:00      US    Android    shmoo         10

15:00      US    Android    foo           5

15:00      US    IOS        shmoo         8

15:00      US    IOS        foo           4

15:00      US    UNKNOWN    shmoo         4

Right table:

datehour  geo      platform   revenue

15:00     US       Android    5$

15:00     US       IOS        10$

15:00     UNKNOWN  IOS        2$

Joined table:

15:00   US      Android    shmoo         10  15  (5$/15)*10 = 3.333..3

15:00   US      Android    foo           5   15  (5$/15)*5 = 1.6666..7

15:00   US      IOS        shmoo         8   12  (10$/12)*8 = 6.6666..7

15:00   US      IOS        foo           4   12  (10$/12)*4 = 3.3333..3

15:00   US      UNKNOWN    shmoo         4   4   0$

15:00   UNKNOWN IOS        null          0   0   2$

sum(row_revenue) = 17$

Thanks all !