1. Crypto Market Algorithm Report

A number of algorithms are used to mine cryptocurrencies. As a part of comparison, create a query to return a list of algorithms and their volumes to each quarter of year 2020.

The results should be in the following format: algorithm name Q1, Q2, Q3,Q4 transactions.

  • Q1 through Q4 contain the sum of transaction volumes for the algorithm for each calendar quarter of 2020 precise to 6 places after the decimal.
  • Results should be sorted ascending by algorithm name.

Schema

There are 2 tables:

Sample Data Tables

For the sample data in tables:

My Solutions

Option 1: Use LEFT JOIN



WITH quarterly_volume AS (
  SELECT 
    algorithm, 
    SUM(volume) AS volume, 
    DATEPART(QUARTER, dt) AS quarters 
  FROM 
    coins c 
    JOIN transactions t ON t.coin_code = c.code 
  WHERE 
    DATEPART(YEAR, dt) = 2020 
  GROUP BY 
    algorithm, 
    DATEPART(QUARTER, dt)
) 
SELECT 
  c.algorithm, 
  qv1.volume AS transactions_Q1, 
  qv2.volume AS transactions_Q2, 
  qv3.volume AS transactions_Q3, 
  qv4.volume AS transactions_Q4 
FROM 
  coins c 
  LEFT JOIN quarterly_volume qv1 ON c.algorithm = qv1.algorithm 
  AND qv1.quarters = 1 
  LEFT JOIN quarterly_volume qv2 ON c.algorithm = qv2.algorithm 
  AND qv2.quarters = 2 
  LEFT JOIN quarterly_volume qv3 ON c.algorithm = qv3.algorithm 
  AND qv3.quarters = 3 
  LEFT JOIN quarterly_volume qv4 ON c.algorithm = qv4.algorithm 
  AND qv4.quarters = 4 
WHERE 
  c.code NOT LIKE 'DOGE' 
ORDER BY 
  algorithm

Option 2: Use Pivot

WITH quarter_part AS (
  SELECT 
    *, 
    DATEPART(QUARTER, t2.dt) AS Quarter 
  FROM 
    coins AS t1 
    JOIN transactions t2 ON t1.code = t2.coin_code 
  WHERE 
    DATEPART(YEAR, t2.dt) = 2020
), 
summary_part AS (
  SELECT 
    algorithm, 
    Quarter, 
    SUM(volume) AS vol 
  FROM 
    quarter_part 
  GROUP BY 
    algorithm, 
    Quarter
) 
SELECT 
  algorithm, 
  [1] AS transactions_Q1, 
  [2] AS transactions_Q2, 
  [3] AS transactions_Q3, 
  [4] AS transactions_Q4 
FROM 
  summary_part p PIVOT (
    SUM(vol) FOR Quarter IN ([1], [2], [3], [4])
  ) AS pvt

2. Crypto Market Suspicious Transaction

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.

Schema

There is 1 table:

Sample Data Tables

For the sample data in tables:

My Solution

Correct Hackerrank result:

SELECT sender,
       MIN(dt)     AS sequence_start,
       MAX(dt)     AS sequence_end,
       COUNT(*)    AS transactions_count,
       SUM(amount) AS transactions_sum
FROM (
     SELECT *,
            COUNT(grp1) OVER ( PARTITION BY sender ORDER BY dt ) AS grp2

     FROM (
          SELECT *,
                 CASE
                     WHEN DATEDIFF(MINUTE, LAG(dt) OVER ( PARTITION BY sender ORDER BY dt ),
                                   dt
                              ) > 60 THEN 1
                     END AS grp1
          FROM [dbo].[Crypto]
      ) sub1
 ) sub2
GROUP BY sender,
         grp2
HAVING SUM(amount) >= 150
   AND COUNT(*) > 1

Other option 1: Use Windows Function ROW_NUMBER (Easy to know but not match Hackerrank since this combine all in one group)

WITH difference AS (
    SELECT *
        , DATEDIFF(MINUTE, LAG(dt) OVER (ORDER BY sender, dt), dt) AS difF_minute
        , ROW_NUMBER() OVER (ORDER BY sender, dt) AS rownumber
    FROM [dbo].[Crypto]
),
rn AS (
    SELECT rownumber
    FROM difference
    WHERE rownumber IN (
      SELECT rownumber
      FROM [dbo].[Crypto]
      WHERE ABS(diff_minute) < 60
    )
),
sequences_ as (
    SELECT *
    FROM difference
    WHERE rownumber IN(
        SELECT rownumber
        FROM rn
        UNION
        SELECT rownumber - 1 as rownumber
        FROM rn
	)
)
SELECT sender
    , MIN(dt) AS Sequence_start
    , MAX(dt) AS Sequence_end
    , COUNT(rownumber) AS transactions_count
    , SUM(amount) AS transactions_sum
FROM sequences_
GROUP BY sender
HAVING SUM(amount) >= 150
ORDER BY sender, MIN(dt), MAX(dt)

Other option 2: Only care suspicious transaction

WITH difference AS (
    SELECT *
        , DATEDIFF(MINUTE, LAG(dt) OVER (ORDER BY sender, dt), dt) AS diff_minute
        , ROW_NUMBER() OVER (ORDER BY sender, dt) AS rownumber

    FROM [dbo].[Crypto]
)


SELECT sender
    , MIN(dt) AS Sequence_start
    , MAX(dt) AS Sequence_end
    , COUNT(rownumber) AS transactions_count
    , SUM(amount) AS transactions_sum
FROM difference
WHERE ABS(diff_minute) < 60
GROUP BY sender
HAVING SUM(amount) >= 150
ORDER BY sender, MIN(dt), MAX(dt);