Expectaions:
Write a SQL query that retrieves user_ids with the following pattern (in sequence):
a) Make one or more fiat deposits summing at least 2k EUR. The first fiat deposit is within 4 days after the onboarding. These are the first deposits of the user, with no crypto deposits before.
b) Exchange most of the funds (≥ 90%) to ETH or BTC in one transaction
c) Withdraw at least 2k EUR of ETH or BTC within 4 days after the first fiat deposit.This crypto withdrawal is the first withdrawal by the user.
user_info.csv
| columnname | explanation | |||
|---|---|---|---|---|
| user_id | The user’s unique identifier | |||
| age | The age of the user in years | |||
| country_code | The user’s country | |||
| was_referred | Boolean indicating if the user was referred in the | |||
| onboarding_completed_at | Timestamp when the user completed onboarding | |||
| t2_upgrade_at | Timestamp when the user upgraded to Tier 2. This | |||
| entails a photo ID address verification, and | ||||
| allows the user to deposit up to 50K EUR and | ||||
| withdraw 1M EUR | ||||
| t3_upgrade_at | Timestamp when the user upgraded to Tier 3. This | |||
| entails a origin of funds verification, and | ||||
| removes deposit or withdrawal limits | ||||
| became_premium_at | Timestamp when the user became premium | |||
| premium_tier Current premium tier of the user | ||||
| is_fraud Boolean indicating the user has been | ||||
| flagged as suspicious by the compliance team |
transaction_info.csv
| columnname | explanation | |||
|---|---|---|---|---|
| user_id | The user’s unique identifier | |||
| transaction_id | The age of the user in years | |||
| timestamp | The transaction’s timestamp | |||
| event_kind | The type of transaction. Possible values: | |||
| fiat_deposit / fiat_withdrawal / crypto_deposit/ | ||||
| crypto_withdrawal / exchange Fiat means | ||||
| “traditional” currencies such as EUR, | ||||
| USD, GBP etc, whereas Crypto means tokens | ||||
| such as BTC, CHSB, ETH etc. | ||||
| Deposit means transferring funds from an | ||||
| external account to SwissBorg, and withdrawal | ||||
| means the opposite. Exchange means trading one | ||||
| currency to another within the app. | ||||
| currency | The currency of the transaction. In case of | |||
| exchanges, it contains the format | ||||
| FromCurrency_ToCurrency. For instance, if | ||||
| BTC_USD, it means that the user traded BTC to | ||||
| USD | ||||
| amount_in_eur | The equivalent EUR amount of the transaction |
CodePudding user response：
Question (c) is not clear to me. How will I know the amount of Euro that is withdrawn? These queries are written as per SQL-SERVER which wouldn't differ much from MySQL, But make sure that you add changes to the date addition syntax just in case if it isn't working in MySQL.
a)
WITH DATA1 AS (
SELECT T1.USER_ID, SUM(T2.AMOUNT_IN_EUR) AS DEPOSITS FROM user_info T1 JOIN transaction_info T2 ON LOWER(T1.USER_ID) = LOWER(T2.user_id)
WHERE T2.event_kind = 'fiat_deposit'
AND
CAST( T2.TIMESTAMP AS datetime2 ) BETWEEN
CAST(T1.onboarding_completed_at AS datetime2) AND DATEADD(DAY, 4, CAST(T1.onboarding_completed_at AS datetime2) )
GROUP BY T1.user_id
)
SELECT DATA1.* FROM DATA1 WHERE DATA1.DEPOSITS >= '20000' ;
b)
WITH DATA1 AS (
SELECT T1.USER_ID, T2.CURRENCY, T2.amount_in_eur,
SUM(T2.AMOUNT_IN_EUR) OVER ( PARTITION BY T1.USER_ID) AS TOTAL_TRANSACTION
FROM USER_INFO T1 JOIN TRANSACTION_INFO T2 ON T1.user_id = T2.user_id
),
DATA2 AS (
SELECT DATA1.*, SUM(DATA1.AMOUNT_IN_EUR) OVER (PARTITION BY DATA1.USER_ID) AS TOTAL_TRANS_ETHBTC
FROM DATA1 WHERE DATA1.CURRENCY IN ('ETH','BTC')
),
DATA3 AS (
SELECT DATA2.*, (DATA2.TOTAL_TRANS_ETHBTC * 100) / DATA2.TOTAL_TRANSACTION AS TRANSACTION_ETHBTC_PERCENTAGE
FROM DATA2
)
SELECT DATA3.USER_ID, DATA3.TOTAL_TRANSACTION, DATA3.TOTAL_TRANS_ETHBTC,
ROUND(DATA3.TRANSACTION_ETHBTC_PERCENTAGE,2) AS ETH_BTC_PERCENT
FROM DATA3
WHERE DATA3.TRANSACTION_ETHBTC_PERCENTAGE >= '90'
GROUP BY DATA3.USER_ID, DATA3.TOTAL_TRANSACTION, DATA3.TOTAL_TRANS_ETHBTC, ROUND(DATA3.TRANSACTION_ETHBTC_PERCENTAGE,2)