datawithdanny / sql-masterclass Goto Github PK

For lesson 3, there is no need for an extra bracket in the last wrong code on the page.

Query on platform as at now

SELECT
  ticker,
  SUM(CASE WHEN price > open THEN 1 ELSE 0 END) / COUNT(*)) AS breakout_percentage,
  SUM(CASE WHEN price < open THEN 1 ELSE 0 END) / COUNT(*)) AS non_breakout_percentage
FROM trading.prices
WHERE market_date >= '2019-01-01' AND market_date <= '2019-12-31'
GROUP BY ticker;

Correct Query

SELECT
  ticker,
  SUM(CASE WHEN price > open THEN 1 ELSE 0 END) / COUNT(*) AS breakout_percentage,
  SUM(CASE WHEN price < open THEN 1 ELSE 0 END) / COUNT(*) AS non_breakout_percentage
FROM trading.prices
WHERE market_date >= '2019-01-01' AND market_date <= '2019-12-31'
GROUP BY ticker;

Question 3 and 4 are missing a WHERE clause to specify BTC/ETH.
Question 5 eth_sell_qty is actually the BTC sell qty.

My transactions table is missing the txn_date column, I am using the schema provided

The question is For each year, calculate the following buy and sell metrics for Bitcoin:

The SQL answer is :
SELECT
EXTRACT(YEAR FROM txn_date) AS txn_year,
txn_type,
COUNT(*) AS transaction_count,
ROUND(SUM(quantity)::NUMERIC, 2) AS total_quantity,
ROUND(AVG(quantity)::NUMERIC, 2) AS average_quantity
FROM trading.transactions
GROUP BY txn_year, txn_type
ORDER BY txn_year, txn_type;

Missing Where ticker='BTC'

The answer table is wrong as well.

There are two Question 3, asking for different questions.

What is the monthly average of the price column for Ethereum in 2020? Sort the output in chronological order and also round the average price value to 2 decimal places

Question 6 asks to calculate monthly average price for Ethereum in 2020, however the solution is missing a where clause for the same.

Current Solution

SELECT
  DATE_TRUNC('MON', market_date) AS month_start,
  -- need to cast approx. floats to exact numeric types for round!
  ROUND(AVG(price)::NUMERIC, 2) AS average_eth_price
FROM trading.prices
WHERE EXTRACT(YEAR FROM market_date) = 2020
GROUP BY month_start
ORDER BY month_start;

Correct Solution

SELECT
  DATE_TRUNC('MON', market_date) AS month_start,
  -- need to cast approx. floats to exact numeric types for round!
  ROUND(AVG(price)::NUMERIC, 2) AS average_eth_price
FROM trading.prices
WHERE EXTRACT(YEAR FROM market_date) = 2020 AND ticker = 'ETH'
GROUP BY month_start
ORDER BY month_start;

Hello @datawithdanny ,
I am currently facing an issue while running SQL script on SQL workbench. The issue occurs when I am trying to load the data in the transaction table. It says there is an incorrect date-time value for id 774.

Please let me know what can be done in this case.

Hi @datawithdanny

I have run docker -compose up and it builds successfully. When I open localhost : 3000 , it's not working

here are some ref screenshots :-

How to fix this issue?
Thanks

For that question, the answer should be 11. Instead, there is a list of the different countries which was the answer for the previous question. I guess it's just a small mistake however, I just want to point out for future improvement.

Thank You for this course

The solution for Question 6 in step4.md is wrong.

Question - Summarise all buy and sell transactions for each member_id by generating 1 row for each member with the following additional columns:

• Bitcoin buy quantity
• Bitcoin sell quantity
• Ethereum buy quantity
• Ethereum sell quantity

Given Solution -

SELECT
  member_id,
  SUM(
    CASE
      WHEN ticker = 'BTC' AND txn_type = 'BUY' THEN quantity
      ELSE 0
    END
  ) AS btc_buy_qty,
  SUM(
    CASE
      WHEN ticker = 'BTC' AND txn_type = 'SELL' THEN quantity
      ELSE 0
    END
  ) AS btc_sell_qty,
  SUM(
    CASE
      WHEN ticker = 'ETH' AND txn_type = 'BUY' THEN quantity
      ELSE 0
    END
  ) AS eth_buy_qty,
  SUM(
    CASE
      WHEN ticker = 'BTC' AND txn_type = 'SELL' THEN quantity
      ELSE 0
    END
  ) AS eth_sell_qty
FROM trading.transactions
GROUP BY member_id;

Issue - In the last case, the ticker is filtered with BTC, it should be ETH.

Correct solution -

SELECT
  member_id,
  SUM(
    CASE
      WHEN ticker = 'BTC' AND txn_type = 'BUY' THEN quantity
      ELSE 0
    END
  ) AS btc_buy_qty,
  SUM(
    CASE
      WHEN ticker = 'BTC' AND txn_type = 'SELL' THEN quantity
      ELSE 0
    END
  ) AS btc_sell_qty,
  SUM(
    CASE
      WHEN ticker = 'ETH' AND txn_type = 'BUY' THEN quantity
      ELSE 0
    END
  ) AS eth_buy_qty,
  SUM(
    CASE
      WHEN ticker = 'ETH' AND txn_type = 'SELL' THEN quantity
      ELSE 0
    END
  ) AS eth_sell_qty
FROM trading.transactions
GROUP BY member_id;

Question specifies "average of the price column for Ethereum", but in answer code and results table the 'ETH' filter is not considered in WHERE clausule.

SELECT
DATE_TRUNC('MON', market_date) AS month_start,
-- need to cast approx. floats to exact numeric types for round!
ROUND(AVG(price)::NUMERIC, 2) AS average_eth_price
FROM trading.prices

WHERE ticker = 'ETH' 
      AND EXTRACT(YEAR FROM market_date) = 2020

GROUP BY month_start
ORDER BY month_start;

</details>
<br>
|      month_start       | average_eth_price |
| ---------------------- | ----------------- |
| 2020-01-01             |            156.65 |
| 2020-02-01             |            238.76 |
| 2020-03-01             |            160.18 |
| 2020-04-01             |            171.29 |
| 2020-05-01             |            207.45 |
| 2020-06-01             |            235.92 |
| 2020-07-01             |            259.57 |
| 2020-08-01             |            401.73 |
| 2020-09-01             |            367.77 |
| 2020-10-01             |            375.79 |
| 2020-11-01             |            486.73 |
| 2020-12-01             |            622.35 |
<br>