Weather Analysis
There is a table with daily weather data over the last 6 months of 2020, including the maximum, minimum, and average temperatures.
Write a query that gives month, monthly maximum, monthly minimum, monthly average temperatures for the six months.
Note: Round the average to the nearest integer.
Solution Implementation
SELECT MONTH(record_date), MAX(data_value) AS max, MIN(data_value) AS min,
round(AVG(CASE WHEN data_type = 'avg' then data_value END)) AS avg
FROM temperature_records
WHERE MONTH(record_date) BETWEEN 7 AND 12
GROUP BY MONTH(record_date)
ORDER BY MONTH(record_date);Crypto Market Transactions Monitoring
As part of a cryptocurrency trade monitoring platform, create a query to return a list of
suspicious transactions.
Suspicious transactions are defined as:
. a series of two or more transactions occur at intervals of an hour or less
. they are from the same sender
. the sum of transactions in a sequence is 150 or greater
A sequence of suspicious transactions may occur over time periods greater than one
hour. As an example, there are 5 transactions from one sender for 30 each. They occur at intervals of less than an hour between from 8 AM to 11 AM. These are suspicious and will all be reported as one sequence that starts at 8 AM, ends at 11 AM, with 5 transactions that sum to 150.
The result should have the following columns: sender, sequence_start, sequence_end,
transactions_count, transactions_sum
. sender is the sender’s address.
. sequence_start is the timestamp of the first transaction in the sequence.
. sequence_end is the timestamp of the last transaction in the sequence.
. transactions_count is the number of transactions in the sequence.
. transactions_sum is the sum of transaction amounts in the sequence, to 6 places after
the decimal.
Order the data ascending, first by sender, then by sequence_start, and finally
by sequence_end.
Solution Implementation
WITH ranked_transactions AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY sender ORDER BY transaction_timestamp) AS rn
FROM transactions
),
time_diff AS (
SELECT *,
DATEDIFF(MINUTE, LAG(transaction_timestamp) OVER (PARTITION BY sender ORDER BY transaction_timestamp), transaction_timestamp) AS time_diff
FROM ranked_transactions
)
SELECT sender,
MIN(transaction_timestamp) AS sequence_start,
MAX(transaction_timestamp) AS sequence_end,
COUNT(*) AS transactions_count,
ROUND(SUM(amount), 6) AS transactions_sum
FROM time_diff
GROUP BY sender, rn - ROW_NUMBER() OVER (PARTITION BY sender ORDER BY transaction_timestamp)
HAVING COUNT(*) >= 2 AND SUM(amount) >= 150
ORDER BY sender, sequence_start, sequence_end;The Above Solution Help you to get the Certificate in SQL Advance