Write SQL using HAVING and window functions

Context

You work on fraud analytics. Assume the following schema (PostgreSQL-like types):

transactions

• txn_id BIGINT (PK)
• merchant_id BIGINT
• user_id BIGINT
• region TEXT
• amount_usd NUMERIC(12,2)
• created_at TIMESTAMP (UTC)
• is_fraud BOOLEAN

Question 1 (uses HAVING)

For the last 30 days (relative to CURRENT_DATE, assume UTC), find merchants that:

• Have at least 100 transactions , and
• Have a fraud rate > 3% (fraud rate = #fraud_txns / #total_txns ).

Output columns: merchant_id, total_txns, fraud_txns, fraud_rate.

Question 2 (uses window functions)

For each user_id, compute for every transaction:

• The user’s running count of transactions in the prior 7 days ending at the transaction time (inclusive).
• The user’s rank of the transaction by recency (1 = most recent transaction for that user).

Output columns: txn_id, user_id, created_at, txns_last_7d, recency_rank.