CODEWITHSUNDEEP
teradata
Cupcakke345
Cupcakke345

Reputation: 11

Answer Query in Teradata

Query: 2. List the top 5 shoppers (number) who spent the most on Martin Luther King Jr. Day in 2000. (Hint: Use SELECT TOP 5….)

SELECT TOP 5 Member_Dimension.Member_Key AS "Top 5 Members", Total_Scan_Amount As "Total Scan Amount"
FROM Item_Scan_Fact, Member_Dimension, Date_Dimension
WHERE Item_Scan_Fact.Member_Key = Member_Dimension.Member_Key
  AND Item_Scan_Fact.Transaction_Date_Key = Date_Dimension.Date_Key
  AND Date_Dimension.Year_Number = 2000
  AND Date_Dimension.Day_Description LIKE 'Martin';

My code doesn't return any values, just an empty table. I think the source of the error is the Day_Description, but I am not sure. I believe this because when I remove the Day_Description = under the WHERE clause, the code does return a table of 5 values.

This is on teradata under the UA_SAMSCLUB_STAR. Thank you so much!

Upvotes: 1

Views: 73

Answers (1)

dnoeth
dnoeth

Reputation: 60462

Your problem is the Date_Dimension.Day_Description LIKE 'Martin' condition, which returns an exact match only. Try Date_Dimension.Day_Description LIKE 'Martin%' instead.

And as Tim already noted add ORDER BY Total_Scan_Amount DESC to get the highest amounts.

Upvotes: 1

Related Questions