SQLPad Forum question: Weird sensitivity to decimal points

Weird sensitivity to decimal points

Hi!

I noticed some pretty funky sensitivity out in the 9th/10th decimal places in some problems. Queestion #114 is an example. My code is below - seems to generate the right answer, but the console marks it as wrong - only difference seems to be out in the 9th/10th decimal place. Maybe better practice would be to edit the question to explicitly ask for set number of decimal points to avoid confusion?

-- Question 114 -> marked wrong by console -- 
with cte_1 AS (
select
    o.order_dt,
    sum(o.qty*o.unit_price_usd) as revenue
from orders o
group by 1)

select
    order_dt,
    (revenue - LAG(revenue,1) OVER ( order by order_dt asc))::numeric* 100 /
    (LAG(revenue,1) OVER (order by order_dt asc))::numeric as dod_change
from cte_1
order by 1 asc

Answers

Comment

Hey Eugene,

Thanks for your question, your solution does look right.

Unfortunately, our OJ uses assert to check your results against our 'official' solution, that's why it has to match 100% to pass.

It's not easy to add a 'tolerance' level to the OJ, considering the diversity of the questions, some return strings, some integers, and some are numerics.

And even for numeric types, there are different interpretations regarding the underlying implementation, e.g., Python vs. Postgres vs. MySQL.

If you have suggestions (and how we can technically implement them), happy to hear them and keep improving the product.

Thanks,

Leon

Leon (949)

Nov. 11, 2022, 9:31 a.m.