![Daily life of a Data Engineer](/img/default-banner.jpg)
- Видео 20
- Просмотров 61 042
Daily life of a Data Engineer
Добавлен 24 сен 2022
SQL - Daily life of Data Engineer
#sql Interview question to identify range of status with given date #dataengineering #dataanalytics
Hi All,
My name is Ankit Shrivastava and I am a Data Engineer.
Today this #vlog is regarding #sql Interview question to identify range of status with given date #dataengineering #dataanalytics
DDL for practice:-
create table test_status (date_value date,state varchar(100));
insert into test_status values ('01-01-2024','Success');
insert into test_status values ('01-02-2024','Success');
insert into test_status values ('01-03-2024','Success');
insert into test_status values ('01-04-2024','Fail');
insert into test_status values ('01-05-2024','Fail');
insert into test_status values ('01-06-2024','Success');
Please find the link of my all previous videos:-
#aws S3-IAM-Redshift | Copy data from S3 to Red...
My name is Ankit Shrivastava and I am a Data Engineer.
Today this #vlog is regarding #sql Interview question to identify range of status with given date #dataengineering #dataanalytics
DDL for practice:-
create table test_status (date_value date,state varchar(100));
insert into test_status values ('01-01-2024','Success');
insert into test_status values ('01-02-2024','Success');
insert into test_status values ('01-03-2024','Success');
insert into test_status values ('01-04-2024','Fail');
insert into test_status values ('01-05-2024','Fail');
insert into test_status values ('01-06-2024','Success');
Please find the link of my all previous videos:-
#aws S3-IAM-Redshift | Copy data from S3 to Red...
Просмотров: 490
Видео
#sql query to find all numbers that appear at least three times consecutively #dataanalysts #data
Просмотров 3,1 тыс.Год назад
Hi All, My name is Ankit Shrivastava and I am a Data Engineer. Today this #vlog is regarding #sql - SQL query to find all numbers that appear at least three times consecutively DDL for practice:- create table num_test (id int,num int) insert into num_test values (1,1); insert into num_test values (2,1); insert into num_test values(3,1); insert into num_test values(4,2); insert into num_test val...
#sql - Multiple ways to create table in #database | with data, without data, with filter condition
Просмотров 331Год назад
Hi All, My name is Ankit Shrivastava and I am a Data Engineer. Today this #vlog is regarding #sql - Multiple ways to create table in #database | with data, without data, with filter condition DDL for practice:- CREATE TABLE emp1 (id integer, name varchar(50)); insert into emp1 values(1,'A'); insert into emp1 values(2,'B'); Please find the link of my all previous videos:- #aws S3-IAM-Redshift | ...
#sql - Calculate business day between two dates including weekend, holiday and if holiday on weekend
Просмотров 2,3 тыс.Год назад
Hi All, My name is Ankit Shrivastava and I am a Data Engineer. Today this #vlog is regarding #sql Calculate total number of business days between two given dates considering following factors : 1. Consider weekends 2. Consider holidays 3. Consider what if your holiday is coming on weekends. Please find the DDL for practice:- create table order_details (id integer, start_date date,end_date date)...
#aws S3-IAM-Redshift | Copy data from S3 to Redshift and vice versa using IAM role & policy #amazon
Просмотров 6 тыс.Год назад
Hello everyone, My name is Ankit Shrivastava and I am a Data Engineer. Today this #vlog is regarding Copy data from S3 to Redshift and vice versa using IAM role & policy Now a days Cloud services are playing major role in data driven projects and if you are using AWS services (Amazon Web Services) in your project then this video is for you, which will help you to understand how S3-IAM-Redshift ...
#sql interview question How to achieve Organisational hierarchy using self join from one table | DE
Просмотров 1,2 тыс.Год назад
Hi All, My name is Ankit Shrivastava and I am a Data Engineer. Today this #vlog is regarding #sql Interview question How to achieve Organisational hierarchy using self join from one table. Please find the DDL for practice:- create table employees (emp_id integer, emp_name varchar(20),mgr_id integer) insert into employees values (1,'Ankit',2); insert into employees values (2,'Rahul',4); insert i...
#sql Count occurence of character in a string / Count occurrence of word in a sentence #data analyst
Просмотров 874Год назад
Hi All, My name is Ankit Shrivastava and I am a Data Engineer. Today this #vlog is regarding #sql Interview question How to Count occurrence of any character in a string (case sensitive and case insensitive) & Count occurrence of word in a sentence #sqlinterviewquestionsandanswers #dataanalysts #dataengineering #dataanalysis Please find the link of my all previous videos:- #sql - difference bet...
#sql - difference between WHERE & AND clause with LEFT/RIGHT join | INNER join+WHERE=LEFT join+WHERE
Просмотров 2,3 тыс.Год назад
Hi All, My name is Ankit Shrivastava and I am a Data Engineer. Today this #vlog is regarding #sql What is the difference between WHERE & AND clause with LEFT/RIGHT join ? What is happening when we use same condition with WHERE clause & AND clause with JOINS and also I am discussing INNER join WHERE clause is giving same result with LEFT join WHERE clause Is output is going to be same ? select *...
#sql How to handle NULL while aggregation | SUM() | AVG() | Data Analyst | Data Engineer |Math calc
Просмотров 1,6 тыс.Год назад
Hi All, My name is Ankit Shrivastava and I am a Data Engineer. Today this #vlog is regarding #sql How to handle NULL while aggregation or performing any mathematical calculation. Please find the below DDL for practice:- create table product_sales (prd_id int,sale_amt int) insert into product_sales values (1,100); insert into product_sales values (2,200); insert into product_sales values (3,null...
#sql Calculate start & end time and average time spent in successful transaction | startup interview
Просмотров 5 тыс.Год назад
Hi All, My name is Ankit Shrivastava and I am a Data Engineer. Today this #vlog is regarding #sql How to Calculate start & end time of transaction and average time spent in one successful transaction. This #sql question was asked in one of the #startup to get the average time taken by users to complete successful transaction and based on that they wanted to improve the transaction time and the ...
#sql to achieve last NOT NULL value from the record | Analytical Function | Data Engg | Data Analyst
Просмотров 2,5 тыс.Год назад
Hi All, My name is Ankit Shrivastava and I am a Data Engineer. Today this #vlog is regarding #sql to achieve last NOT NULL value from the record list. This question is becoming common now a days for #etl developers #etltesting #dataengineering #dataanalysts #datanalysis #datanalytics #bigdata Please find the DDL statement below for exercise;- create table last_not_null (id integer, product_qty ...
#sql to identify Top Performing Product every Quarter of each year | Highest sale every quarter
Просмотров 2,4 тыс.Год назад
Hi All, My name is Ankit Shrivastava and I am Data Engineer. Today this #vlog is regarding mostly asked #sql #sqlinterviewquestions for #etl developers #bigdata developers #datanalytics #dataengineering #dataanalysts #etltesting roles. #sql Interview Question - Identify Top Performing Product every Quarter of each year or identify one product having Highest sale every quarter for #marketing #pr...
#sql Interview Question - Calculate Running Total | Cumulative sum | UNBOUNDED PRECEDING FOLLOWING
Просмотров 2,3 тыс.Год назад
Hi All, My name is Ankit Shrivastava and I am Data Engineer. Today this #vlog is regarding mostly asked #sql #sqlinterviewquestions How to calculate Running total/Cumulative Sum using #sql . Also we have discussed how we can use UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING and CURRENT ROW with sql window function. Please find the below DDL we have discussed for practice:- create table sales ( sa...
#sql Interview question Schedule cricket match between teams and generate points table using SQL
Просмотров 8 тыс.Год назад
Hi All, This is Ankit Shrivastava and I am working as Data Engineer. Today this #vlog is regarding mostly asked #sql #sqlinterviewquestions . This #vlog is regarding how we can schedule cricket match between teams and generate points table. This is very interesting SQL interview question having very simple approach. In this video I have used self join to case statement to generate the required ...
#sql Interview Questions - All JOINS (INNER, LEFT, RIGHT, FULL OUTER) | JOIN only NULL values output
Просмотров 8 тыс.Год назад
Hi All, This is Ankit Shrivastava and today this #vlog is regarding mostly asked #sql #sqlinterviewquestions . This #vlog is regarding is All the SQL joins INNER LEFT, RIGHT, FULL OUTER on NOT NULL as well as NULL values. This is very common SQL interview question in which interviewer will check your understanding on all the joins and that too with #null values and as well as #notnull values. P...
#sql Interview Question - What is the difference between COUNT(*), COUNT(1) and COUNT(-1)
Просмотров 4,9 тыс.Год назад
#sql Interview Question - What is the difference between COUNT(*), COUNT(1) and COUNT(-1)
#SQL Interview Question - How to delete duplicate record from table
Просмотров 1,8 тыс.Год назад
#SQL Interview Question - How to delete duplicate record from table
#SQL Interview Question -Second highest salary using dense rank analytical function || 3rd highest
Просмотров 854Год назад
#SQL Interview Question -Second highest salary using dense rank analytical function || 3rd highest
#SQL - Interview Question- SQL to pull unique record after combination of column #SELFJOIN
Просмотров 2,7 тыс.Год назад
#SQL - Interview Question- SQL to pull unique record after combination of column #SELFJOIN
#SQL - Interview Questions - Join on duplicate values all four joins (INNER, LEFT, RIGHT, FULL OUTER
Просмотров 4 тыс.Год назад
#SQL - Interview Questions - Join on duplicate values all four joins (INNER, LEFT, RIGHT, FULL OUTER
Excellent
WITH CTE AS(SELECT *, EXTRACT(YEAR FROM sale_Date) AS YEAR, quarter(sale_Date) AS QUARTER FROM prd_sales),V1 AS( SELECT *, CONCAT(YEAR,'-','Q',QUARTER) AS QUARTERS FROM CTE),V2 AS( SELECT Product_id,Product_name,SUM(sale_qty) AS sale_qty,QUARTERS FROM V1 group by 1,2,QUARTERS),V3 AS( SELECT *, dense_rank()OVER(partition by QUARTERS ORDER BY sale_qty DESC) AS RNK FROM V2) SELECT * FROM V3 WHERE RNK = 1;
WITH cte AS ( SELECT product_id, product_name, sale_date, sale_qty, EXTRACT(YEAR FROM sale_date) AS year, CEIL(EXTRACT(MONTH FROM sale_date) / 3) AS quarter, SUM(sale_qty) AS total_sales_qty GROUP BY product_id, product_name, year, quarter ) cte1 AS ( SELECT product_id, product_name, sale_date, sale_qty, year, quantity, ROW_NUMBER() OVER (PARTITION BY year, quarter ORDER BY total_sales_qty DESC) AS rnk FROM cte ) SELECT Product_id, Product_name, year, quarter, total_sales_qty FROM cte WHERE rnk = 1 ORDER BY year, quarter;
with cte3 as (select distinct a.product_id,a.product_name,c.sale as sale_qty,concat(a.year_,'-Q',a.quarter) from( (select product_id,product_name,sale_date,Extract(Quarter from sale_date) as quarter ,Extract(Year from sale_date) as year_ from prd_sales) a join (with cte as (select product_id,product_name,Extract(Quarter from sale_date) as quarter ,Extract(Year from sale_date) as year_ ,sum(sale_qty) sale FROM prd_sales group by 1,2,3,4 order by 3,1), cte2 as (select *, rank() over(partition by quarter,year_ order by sale desc,year_) rn from cte) select * from cte2 where rn=1) c on c.quarter=a.quarter and c.year_=a.year_ and c.product_id=a.product_id and a.product_name=c.product_name)) select * from cte3
Very nicely explained
Thank you, very helpful, please make more trick question videos.
Datetime query is not working in oracle sql developer
select aa.* from landscape aa join landscape bb on aa.sourcename=bb.destination and aa.destination=bb.sourcename and aa.destination>bb.destination union select aa.* from landscape aa left join landscape bb on aa.sourcename=bb.destination and aa.destination=bb.sourcename where bb.sourcename is null
really helpful✌🤟
select * from distance where source_city < destination_city
Great video.
Hi Folks I have a different approach : WITH cte AS (select *, case when sale_Date between '2021-01-01' and '2021-03-01' then 'Q1-2021' when sale_Date between '2021-04-01' and '2021-06-01' then 'Q2-2021' when sale_Date between '2021-07-01' and '2021-09-01' then 'Q3-2021' when sale_Date between '2021-10-01' and '2021-12-01' then 'Q4-2021' when sale_Date between '2022-01-01' and '2022-03-01' then 'Q1-2022' end as "Quater" from prd_sales ),cte1 as (select *,sum(sale_qty) over (partition by Quater,Product_id ) Total from cte),cte2 as ( select Product_id,Product_name,Quater,sale_Date,max(Total) over (partition by Quater,Product_id) Top1 from cte1),cte3 as (select distinct Product_id,Product_name,Quater,Top1,DENSE_RANK() over (partition by Quater order by Top1 desc) rn from cte2) select * from cte3 where rn = 1 order by Quater
Thanks a lot Sir, for this tutorial. Need more videos like this.🙏
In case we don’t want to unload, and we just want to copy then, can we use ‘copy’ command to copy redshift data files into s3 ? @dailylifeofadataengineer7742
Why do you keep asking "OK?" and "Right?" like nobody is there to answer you buddy, you are having a delusion
Hi, you require a MIC. Please use 🙂
Lets Suppose if we want to find 10th consecutive number then writing multiple lead function is not good. Please provide a more generic solution
with cte as( Select *,count(product_qty)over(order by(select null) rows between unbounded preceding and 0 following) as grp from last_not_null) Select id,first_value(product_qty)over(partition by grp order by grp) as product_qty from cte
row_number is better but nice try sir
for cross jion nulls will be ingnored right ?
Thanks, great guidance.
Thank you
Please more videos upload from windows function, CTE , store prodeure,
Upload more videos on windows function
Sure, Thank you
Thank you for nice info, i am looking for same id 1 ad logged in again and logged out for second time and 3rd time, how to calculate each time, please help me
Then how to get only last quarter Total sales here
Thanks. That helped me a lot.😍
with data as (select distinct num, lead(num,1) over(order by num ) as x1, lead(num,2) over(order by num ) as x2 from Logs )select distinct num as ConsecutiveNums from data WHERE num=x1 AND x1 = x2;
its not working when i tried solving leetcode please help me
Which SQL query is faster? Filter on Join criteria or Where clause? Please give the ans of above question.
Please focus on explaining clearly, and use the right example its very confusing, terms not used correctly
thank you it was really helpful. you have used three query regarding with regards to "unbounded preceding and unbounded following": if we use sum function instead of count and use "unbounded preceding and unbounded following" the output will be "55" if we use sum function instead of count and use "unbounded preceding and current row" the output will be "1, 3,6,....55" if we use sum function instead of count and use "current row and unbounded following" the output will be "55, 45, ....3,1" please correct me if my understanding is not correct
Very detailed. Helped me in one of the interviews. Thanks for sharing such sessions.
Hi .... Wat is the name of this SQL IDE ???
Informative problem and solution.
Thank you for watching the video. Please share within your circle for maximum reach
Hi Ankur, The videos are very informative. Could you please post the videos of the python questions you came across in your interviews? The SQL part is really great, if we can have python questions too, then it will add feather to the wings. It will level up the quality of content according to me. Thanks for taking time to read this comment/suggestion
Sure will do Thanks for watching
WITH quarter_data as ( SELECT *, concat(year(sale_date),'-Q', datepart(quarter,sale_date)) as quarter from prd_sales ), ranked_data AS ( SELECT *, dense_rank() over(partition by quarter order by sale_qty DESC) AS drn ,sum(sale_qty) over(partition by quarter, product_id) AS total_quarter_sale FROM quarter_data ) SELECT product_id, product_name, total_quarter_sale, quarter FROM ranked_data WHERE drn = 1
Super
Thank you for watching
we can use NVL function like select avg(nvl(sale_amt,0)) as avg_sal from product_sales
😅
Bring mmore.
Sure, Thank You for watching
Nice approach❤. If first_value or last_values is not support in sql version then do you have any other approach? Please share.
thankyou so much for the video
Thank you for watching
Nice vide with perfect explanation.
Thank you for watching
Nicely explained Ankit.
Thank you for watching
thanks. That helped me a lot.
Awesome, Thank you for watching
Wow! Ankit you are taking unique cases to solve in SQL and this will definitely helps to upgrade the SQL concepts Thanks man👍
Thanks Srikanth , Please do share with your friends for maximum reach
thanks sir 🙏
Thank you for watching the video
Create a video duplicate records with timestamp
Sure, Thank you for watching
Bro I have one question how to send it
Hi @9550446173 Lets connect on linkedin to discuss more on this www.linkedin.com/in/ankit-shrivastava-5262561a/
How to calculate the working hours excluding the non working hours
Hi Nasir We can multiply 8 with actual working days
select * from distance where source_city < destination_city;
Select * from distance where source_city in ('Mumbai','Delhi', 'Bangalore') and destination_city in ('Pune', 'Kolkata');