intellidegentQuestion 10 - WHERE vs JOIN Filter
Updated 2023-01-28
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
›
⌄
WITH TOP_TEN_SALES AS (
SELECT
CURRENCY_SYMBOL "Coin",
COUNT(TX_HASH) "Transactions",
SUM(PRICE_USD) "US_Sales",
ROW_NUMBER() OVER(ORDER BY SUM(PRICE_USD) DESC) AS "US_Sales_Rank"
FROM ethereum.core.ez_nft_sales
WHERE BLOCK_TIMESTAMP::date BETWEEN '2022-12-01' AND '2022-12-31'
and CURRENCY_SYMBOL IS NOT NULL AND PRICE_USD IS NOT NULL
GROUP BY CURRENCY_SYMBOL
ORDER BY "US_Sales" DESC
LIMIT 10)
SELECT "Coin", "US_Sales_Rank", "US_Sales", AVG(PRICE) AS "Avg_US_Price"
FROM TOP_TEN_SALES
-- In this case I use LEFT OUTER JOIN, but ETH and nftx_token is not appearing
-- This is because I need to find the average price from the fact_hourly_token_prices table,
-- and it needs to be for the month of December 2022.
-- In this case I filter by date using the WHERE clause.
-- Since there is not ETH or nftx_token for that month, it gets filtered out.
LEFT OUTER JOIN ethereum.core.fact_hourly_token_prices FHTP ON TOP_TEN_SALES."Coin" = FHTP.SYMBOL
WHERE FHTP.HOUR::DATE BETWEEN '2022-12-01' AND '2022-12-31'
GROUP BY "Coin", "US_Sales_Rank", "US_Sales"
ORDER BY "US_Sales_Rank"
Run a query to Download Data