Why is the Execute V2 total sales output (358315.09) not the absolute value of the Execute V1 total sales output (-190571.46)? The only difference in the code generated by the LLM is the two cases is the sign change on the qty_delta value, and in both V1 and V2 the code specifies WHERE action = sale.
V1: SUM(qty_delta * unit_price) AS total_sales…
V2: SUM(ABS(qty_delta) * unit_price) AS total_sales…
Hello @wgs11,
Based on the two versions you provide here, it totally makes sense that their difference should be more than just a minus sign. It’s easier to see with with an example. Let’s say qty_delta contains 1, 2, -1 whereas unit_price contains 1, 1, 1, V1 will give us 1 + 2 -1 = 2 while V2 will give us 1 + 2 + 1 = 4.
Cheers,
Raymond
Hi @rmwkwok - When action = sale, as is the case here, the sign on qty_delta will always be negative. I believe in this case we aren’t summing up cases where there is a mix of positive and negative qty_delta values.
Hello @wgs11,
Thank you for the additional info. I somehow didn’t have access to the lab yesterday, so my response was not taking the full picture into consideration. Now I have just run the lab and -190571.46 turns out to be for the color blue whereas 358315.09 for the color white. If you compared the numbers for the same color, you will get the expected result. However, to do that comparison, without changing existing code, you might add a new cell with the following base code, then copy to it the generated SQL query without “LIMIT 1” so that you see all colors.
utils.execute_sql(<sql query here>, db_path='products.db')
Cheers,
Raymond
Btw, as I read the lab again, the idea I get is that V2 was meant to improve the semantic of the query result by removing the negative sign, so perhaps this is why it was sufficient to see that the sign was removed.
The comparison of the magnitude of the two numbers presented in the last cell could be confusing, as you observed, and it could clear by also noting the name of the color, so to this end, I think it’s fine. However, I have also just filed a suggestion regarding this possible cause of confusion for the team to consider, but in the mean time, for you and any other learners who find this post, for the purpose of verifying the SQL query result, I recommend the suggested work in my last post: