OTP-category indicator flag

Bank Otkritie is building risk scoring by the operation type — payments and regular transfers need different limits and anti-fraud logic. Analytics prepares an indicator to split these streams in the dashboard. Attach an indicator to each row: 1 when category contains payment, and 2 when category contains transfer but does not contain acc. Drop every other row — they don't belong in the dashboard feed. Check the conditions in order: payment wins — a row whose category contains both payment and transfer gets 1.

Expected output sample

This is what a correct answer looks like — its row count is its own, it doesn't have to match the schema tables. Your answer must use the same column names as this sample — alias them with AS if needed.

client_id	session_id	entered_at	status	category	indicator
2	101	2024-04-01T07:05:00.000Z	success	otp-payment	1
3	102	2024-04-01T07:10:00.000Z	success	otp-transfer	2
5	104	2024-04-01T07:20:00.000Z	success	otp-payment-recurring	1

Bank Otkritie

Your query result will appear here

Focus radio

Paused · SomaFM · Fluid