SQL Queries for Data Analysis Skills
Uploaded by
Fãižął QűŗeşhîSQL Queries for Data Analysis Skills
Uploaded by
Fãižął QűŗeşhîCommon questions
Powered by AITo find candidates proficient in Python, Tableau, and SQL, you should execute a SQL query checking for all three skills in each candidate's record. The query would join or filter through a 'skills' table where each candidate's skills are listed. Here's a basic structure of the query: SELECT candidate_id FROM candidates WHERE skills LIKE '%Python%' AND skills LIKE '%Tableau%' AND skills LIKE '%SQL%' ORDER BY candidate_id ASC. This query assumes a candidate's skills are stored in a delimited string format in a column within the same candidate table.
To calculate the CTR, you start by filtering the events from the year 2022. Subsequently, compute the number of clicks and impressions during this period. Use the formula CTR = 100.0 * SUM(clicks) / SUM(impressions) to ensure decimal precision. The SQL query would be something like: SELECT ROUND(100.0 * SUM(clicks) / SUM(impressions), 2) AS CTR_percentage FROM events WHERE event_date BETWEEN '2022-01-01' AND '2022-12-31'. Ensuring the use of 100.0 prevents integer division from occurring .
To combine historical and current week's data from two tables, join both tables using UNION to incorporate all possible records. Summate the play counts while grouping by user_id and song_id. Sort these results to get cumulative play counts: SELECT user_id, song_id, SUM(play_count) AS cumulative_plays FROM (SELECT user_id, song_id, play_count FROM songs_history WHERE date <= '2022-07-31' UNION ALL SELECT user_id, song_id, play_count FROM songs_weekly WHERE date <= '2022-08-04') temp GROUP BY user_id, song_id ORDER BY cumulative_plays DESC .
Start by calculating the daily cumulative balance for each merchant account. Accumulate daily transactions using an aggregate function and reset at month-end: SELECT transaction_date, SUM(amount) OVER (PARTITION BY EXTRACT(YEAR FROM transaction_date), EXTRACT(MONTH FROM transaction_date) ORDER BY transaction_date) as cumulative_balance FROM transactions. Ensure your result resets at the change of each month by using a window partition based on month and year .
To calculate the final balance for each account, the operation must summate all deposits and withdrawals. Using SQL, you can group by account identifier and calculate the sum of deposits minus the sum of withdrawals: SELECT account_id, SUM(deposits) - SUM(withdrawals) AS final_balance FROM transactions GROUP BY account_id. This computes the net of deposits and withdrawals assuming deposits and withdrawals are in separate columns within the same table .