I have a table that looks like this in Google Big Query:
custID InteractionDate Purchased? Sales
1 20150312 F 0
1 20150312 T 200
1 20150314 T 150
2 20150221 T 400
2 20150221 F 0
2 20150221 T 120
..that I need to transform to the following structure:
custID InteractionDate Success Sales
1 20150312 0.5 200
1 20150314 1 150
2 20150221 0.66 520
..where in this second table the success column is defined as
count(purchase=Yes)/(count(purchase=Yes) + count(purchase=No)),
and sales is just the sum.
I'm quite new to BQ and no-SQL languages. I simply don't know how to do this, I've searched and seen examples that suggest using partition over but I simply haven't seen any example that does just this. Thanks a lot in advance.
Aucun commentaire:
Enregistrer un commentaire