on this lab I have created a materialized view as following:
by player (mv_players_purchases_amount)-
SELECT g.id as Id, p.name as Name, '$'||SUM(g.purchases) as Total_Purchases
FROM players_schema.players_data p, players_schema.games_data g
WHERE p.game_details = g.id
GROUP BY g.id, p.name
how do I query the created materialized view to find the total purchases by a player with id 12345. Now I have queried FROM players_schema.players_data p, players_schema.games_data g but I cannot do it FROM by player (mv_players_purchases_amount)... I get an error stating The redshift statement failed. Review it and try again!....
please if anyone out there can help, I would appreciate it.
This is really a Redshift question, don't see why you have the Athena and Glue tags. Test the materialized view query by itself.
yes I have tried the materialized view but it states that Total_Purchases is not queried by the mv_players_purchases_amount... when I query with mv_players_purchases - it works but somehow with _amount it doesn't work. ugh
how would the query go ? has anyone out there done this Cloud Data warehouse Lab on cloud quest under Data Analytics please help.
Update: it gives me and ERROR: column "total_purchases" does not exist in mv_players_purchases_amount
You need to provide more details with a reproducible test case. It should include the DDL for the tables and materialized view and include the SQL query you are executing with the error message.