Wrong output in[11988]_insights-from-data-with-bigquery-challenge-lab

[ashksin121]

use POW instead of POWER in line 210 of [11988]_insights-from-data-with-bigquery-challenge-lab

[elmoallistair]

After reading the documentation i found that POWER() is just synonym of POW(), and i have checked it:

CREATE TEMP FUNCTION test_pow (x INT64, y INT64) AS (POW(x, y));
CREATE TEMP FUNCTION test_power (x INT64, y INT64) AS (POWER(x, y));

SELECT 
  test_pow(3, 4) as pow_3_4,
  test_power(3, 4) as power_3_4,
  test_pow(10, 5) as pow_10_5,
  test_power(10, 5) as power_10_5

Result:

Row	pow_3_4	power_3_4	pow_10_5	power_10_5
1	81.0	81.0	100000.0	100000.0

---

But im wondering why they give different result in case Query 9

Using POWER()

POWER(last_day_cases/first_day_cases, 1/days_diff)-1 as cdgr

Result:

Row	first_day_cases	last_day_cases	days_diff	cdgr
1	7	214420	107	0.10135365333033208

Using POW()

POW(last_day_cases/first_day_cases, 1/days_diff)-1 as cdgr

Result:

Row	first_day_cases	last_day_cases	days_diff	cdgr
1	214420	null	null	null

---

Query:

WITH france_cases AS (
    SELECT
        date,
        SUM(cumulative_confirmed) AS total_cases
    FROM `bigquery-public-data.covid19_open_data.covid19_open_data`
    WHERE country_name="France" AND date IN ('2020-01-24', '2020-05-10')
    GROUP BY date
    ORDER BY date
), 

summary AS (
    SELECT
        total_cases AS first_day_cases,
        LEAD(total_cases) OVER(ORDER BY date) AS last_day_cases,
        DATE_DIFF(LEAD(date) OVER(ORDER BY date),date, day) AS days_diff
    FROM france_cases
    LIMIT 1
)

SELECT 
    first_day_cases, 
    last_day_cases, 
    days_diff, 
    -- POWER(last_day_cases/first_day_cases,1/days_diff)-1 as cdgr  
    -- POW(last_day_cases/first_day_cases,1/days_diff)-1 as cdgr
FROM summary

[ashksin121]

It surely is strange as I got contradictory results when I executed the code. Using POWER, I got null values, while it executed successfully with POW.

[elmoallistair]

Okay I just added a note at 6cb6c39

    -- Note: if you received null in column last_day_cases, days_diff and cdgr
    -- Then try use POW() instead of POWER()
    -- POW(last_day_cases/first_day_cases,1/days_diff)-1 as cdgr

Maybe that will help