Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Normalize pricing on andorid market data
# Prepare: add column 'price_value' and 'price_normalized' to you table
# 1) Run this one first
UPDATE top_android_apps
SET price_value = 0;
# 2) Run this one second
UPDATE top_android_apps
SET price_value = CASE
WHEN ( country IN ( 'GB', 'AU', 'CA', 'HK', 'RU' ) ) THEN
SUBSTRING(price, 4)
WHEN ( country IN( 'BR', 'IT' ) ) THEN REPLACE(
SUBSTRING(price, 3), ',', '.')
WHEN ( country IN ( 'US', 'JP', 'KR' ) ) THEN
REPLACE(SUBSTRING(price, 2), ',', '')
WHEN ( country IN ( 'DE', 'ES' ) ) THEN REPLACE(
LEFT(price, Char_length(price)
- 1), ',', '.')
WHEN ( country IN ( 'SE' ) ) THEN REPLACE(
LEFT(price, Char_length(price) - 2),
',', '.')
ELSE 0
END
WHERE price REGEXP '[0-9]';
# 3) Run this one last
UPDATE top_android_apps
SET price_normalized = CASE
WHEN ( country = 'GB' ) THEN price_value * 1.5917
WHEN ( country = 'AU' ) THEN price_value * 1.0143
WHEN ( country = 'CA' ) THEN price_value * 0.9777
WHEN ( country = 'HK' ) THEN price_value * 0.1286
WHEN ( country = 'RU' ) THEN price_value * 0.0326
WHEN ( country = 'BR' ) THEN price_value * 0.5624
WHEN ( country = 'IT' ) THEN price_value * 1.3538
WHEN ( country = 'US' ) THEN price_value
WHEN ( country = 'JP' ) THEN price_value * 0.0128
WHEN ( country = 'KR' ) THEN price_value * 0.0009
WHEN ( country = 'DE' ) THEN price_value * 1.3538
WHEN ( country = 'ES' ) THEN price_value * 1.3538
WHEN ( country = 'SE' ) THEN price_value * 0.1493
ELSE 0
END;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment