I'm on Snowflake which uses PostgreSQL. DATEADD() and DATE_TRUNC() should be available to you in PostgreSQL For mySQL dateadd is DATE_ADD(), and date_trunc can be achieved using EXTRACT() There is a link to a Stack Overflow post about EXTRACT() for date_trunc here: stackoverflow.com/questions/5541326/postgresqls-date-trunc-in-mysql
@@wjsutton I'll second that DATEADD does not work in the online Postgres editor within the case study-- I had to use 'join_date + 6 as offer_end'; for the DATETRUNC, only DATE_TRUNC() worked in the PostgreSQLv15 environment for me; I'm learning so its a bit confusing at the moment.
join_date + INTERVAL '6 day' as offer_end worked... confusing to me as an inexperienced user, based on different variations of SQL and info available online
Question 4 : -- What is the most purchased item on the menu and how many times was it purchased by all customers? there are two parts: part 1: What is the most purchased item on the menu part 2: how many times was it purchased by all customers Only Part 1 solution is there , Part 2 solution is missing I have given the whole solution for Question 4: Solution:- with most_purchase_item as (select product_id, count(product_id) as total_count from sales group by product_id order by total_count desc limit 1 ), sales_filter_by_most_purchase_item as (select customer_id, count(product_id) as purchase_count from sales as s where s.product_id = (select product_id from most_purchase_item) group by customer_id) select * from sales_filter_by_most_purchase_item;
Thanks a lot. Very Helpfull for beginners
my friend recommended this playlist to me, and it is supererb.
Thank you so much for these videos. Very helpful for me.
Solid work Will
Thanks! :)
great video! Thank you so much
Solid Work! Thanks very much
Much appreciated!
how did you install and load all the schema into snowflake? could you do a tutorial on that?
bro where can i find expected output for each case studies ?
thank you so much
For Question #3 why do we use a CTE instead of a Sub-Query?
they are indistinct, you can choose either way, CTE only helps on clarity and keep things visually ordered.
thank u
From OdinSchool 🖐
sir, which RDBMS are you using in this video and can you tell me the alternate of "dateadd" and "date_trunc" for mysql or PostgreSQL
I'm on Snowflake which uses PostgreSQL.
DATEADD() and DATE_TRUNC() should be available to you in PostgreSQL
For mySQL dateadd is DATE_ADD(), and date_trunc can be achieved using EXTRACT()
There is a link to a Stack Overflow post about EXTRACT() for date_trunc here: stackoverflow.com/questions/5541326/postgresqls-date-trunc-in-mysql
@@wjsutton I'll second that DATEADD does not work in the online Postgres editor within the case study-- I had to use 'join_date + 6 as offer_end'; for the DATETRUNC, only DATE_TRUNC() worked in the PostgreSQLv15 environment for me; I'm learning so its a bit confusing at the moment.
join_date + INTERVAL '6 day' as offer_end worked... confusing to me as an inexperienced user, based on different variations of SQL and info available online
I am using PostgreSQL and I am also getting an error for the DATEADD function
Question 4 : -- What is the most purchased item on the menu and how many times was it purchased by all customers?
there are two parts:
part 1: What is the most purchased item on the menu
part 2: how many times was it purchased by all customers
Only Part 1 solution is there , Part 2 solution is missing
I have given the whole solution for Question 4:
Solution:-
with most_purchase_item as
(select product_id, count(product_id) as total_count from sales
group by product_id
order by total_count desc limit 1
),
sales_filter_by_most_purchase_item as
(select customer_id, count(product_id) as purchase_count from sales as s
where s.product_id = (select product_id from most_purchase_item)
group by customer_id)
select * from sales_filter_by_most_purchase_item;