SQL
Server queries examples
SQL skill assessment
This code was used to respond to the following problem :
Write an SQL query to find the confirmation rate of each user
SELECT
user_id,
round(ifnull((select count(*) from Confirmations c where s.user_id = c.user_id
and action = 'confirmed' )
/
(select count(*) from Confirmations c where s.user_id = c.user_id ),0),2) as confirmation_rate
FROM Signups as s
order by confirmation_rat
SELECT
LEFT(trans_date,7) as month,
country,
COUNT(id) as trans_count,
SUM(state='approved') as approved_count,
SUM(amount) as trans_total_amount,
SUM(CASE
WHEN state='approved'
THEN amount
ELSE 0
END) as approved_total_amount
FROM
Transactions
group by month,country
This code was used to respond to the following problem :
Write an SQL query to find for each month and country, the number of transactions and their total amount, the number of approved transactions and their total amount.