Akash Bhor

#hiringAlert #DataEngineers #NIQ Hi all, NielsenIQ is actively hiring for Data Engineers. Let me know if anybody is interested, I'll be happy to refer. 1. EXP - 3+ Years Data Engineer (Mid) SKILLS - Azure, Spark Scala, SQL    2. EXP - 3-7 Years Data Engineer (Mid) SKILLS - Apache Spark, Databricks, programming language Python/Scala (scala is preferred), Workflow or Orchestration using Airflow tool -good to have. Azure Fundamental Certificate good to have  3. EXP - 7- 10 Years Senior Data Engineer-Python/Scala  SKILLS - Apache Spark, Databricks, programming language Python/Scala (scala is preferred), Workflow or Orchestration using Airflow tool -good to have. Azure Fundamental Certificate good to have    4. EXP - 7- 10 Years Senior Data Engineer- Python     SKILLS - Python & RESTful APIs, pandas, NumPy, flask, Big Data & RDBMS 5. EXP - 10+ Years  Manager Data Engineering- Python, Snowflake cloud database  SKILLS - Python,Snowflake, Azure cloud, SQL,Pytest #NIQ #NielsenIQ #Openings #Referral #Career #Opportunities #DataEngineers #experiencedprofessionals #spark #python #cloud

244

118 Comments

SQL WINDOW FUNCTIONS - create table employee ( emp_ID int , emp_NAME varchar(50) , DEPT_NAME varchar(50) , SALARY int); insert into employee values(101, 'Mohan', 'Admin', 4000); insert into employee values(120, 'Monica', 'Admin', 5000); insert into employee values(121, 'Rosalin', 'IT', 6000); insert into employee values(122, 'Ibrahim', 'IT', 8000); insert into employee values(123, 'Vikram', 'IT', 8000); insert into employee values(124, 'Dheeraj', 'IT', 11000); COMMIT; select * from employee; -- Using Aggregate function as Window Function -- Without window function, SQL will reduce the no of records. select dept_name, max(salary) from employee group by dept_name; -- By using MAX as an window function, SQL will not reduce records but the result will be shown corresponding to each record. select e.*, max(salary) over(partition by dept_name) as max_salary from employee e; -- row_number(), rank() and dense_rank() select e.*, row_number() over(partition by dept_name) as rn from employee e; -- Fetch the first 2 employees from each department to join the company. select * from ( select e.*, row_number() over(partition by dept_name order by emp_id) as rn from employee e) x where x.rn < 3; -- Fetch the top 3 employees in each department earning the max salary. select * from ( select e.*, rank() over(partition by dept_name order by salary desc) as rnk from employee e) x where x.rnk < 4; -- Checking the different between rank, dense_rnk and row_number window functions: select e.*, rank() over(partition by dept_name order by salary desc) as rnk, dense_rank() over(partition by dept_name order by salary desc) as dense_rnk, row_number() over(partition by dept_name order by salary desc) as rn from employee e; -- lead and lag -- fetch a query to display if the salary of an employee is higher, lower or equal to the previous employee. select e.*, lag(salary) over(partition by dept_name order by emp_id) as prev_empl_sal, case when e.salary > lag(salary) over(partition by dept_name order by emp_id) then 'Higher than previous employee'    when e.salary < lag(salary) over(partition by dept_name order by emp_id) then 'Lower than previous employee' when e.salary = lag(salary) over(partition by dept_name order by emp_id) then 'Same than previous employee' end as sal_range from employee e; -- Similarly using lead function to see how it is different from lag. select e.*, lag(salary) over(partition by dept_name order by emp_id) as prev_empl_sal, lead(salary) over(partition by dept_name order by emp_id) as next_empl_sal from employee e; #SQL #Analyticsfunction #Windowfunction

2

Don't confuse yourself what skills are required for a Data engineer because it is in high demand....... Listed here are top skills you must have in your portfolio according to your experience. The most common skills are..... 1. Python 2. SQL 3. Spark 4. Pyspark 5. Hive Check your experience level and starting preparing today. And you don't get lost ever in your path again. Check completed data engineering information & resources here.... https://lnkd.in/g4ZP_AWN Looking for free data engineering Learning path? Check here.... https://zealoq.me/1504b492 SQL Free learning resources. Check here.... https://zealoq.me/14670f48 Pyspark Free learning resources. check here......... https://lnkd.in/gz82NHGp Spark Free learning resources. Check here....... https://lnkd.in/gcZYQC_G Hive Free Learning resources. Check here.... https://lnkd.in/g_HbE6jJ Python Free Learning resources. Check here..... https://lnkd.in/g5PvJ3rN Scala Free Learning resources. Check here.... https://lnkd.in/gSzSiW6h Data Engineering complete free interview questions. check here... https://lnkd.in/g5PvJ3rN Note: There are some skills that absolute no brainer in the image ex: data analysis for 12+ just 12.7% on a rough a note all those skills are equally important but utilization of skills depends on project. hope you don't misguided. Save it for later🙂 do follow Ajay Kadiyala ✅ #dataengineering #python #sql #spark #learningpath #various #experience #interviewexperience #dataanalysis #projects

185

10 Comments

🎉 Excited to Share My LeetCode SQL Playlist! 📊💻 Hi LinkedIn community, I'm thrilled to share that I've created a dedicated playlist in Hindi for LeetCode SQL problems! This collection aims to help everyone enhance their SQL skills through hands-on practice and real-world examples. Topic Covered: 1- All the basics 2- Joins 3- Subquery 4- Window Function 5- Conditional Function #LeetCode #DataScience #TechSkills #SQLPractice #Sharing #Learning #Opportunity

2

Dear Data Engineers, Had an interview for Data Engineer role with 30 to 36 LPA (question 2) During the interview, the interviewer started asking questions on SQL after python & pyspark. Can you help to solve this question? --==== start Question: We are analyzing sales data since year 2010 till current year i.e 2024. We need to generate some report for each financial quarter. Write a SQL query to generate the template for all years and 4 quarters (q1, q2, q3, q4). For example: year | quarter 2010 | Q1 2010 | Q2 2010 | Q3 2010 | Q4 2011 | Q1 . . . 2024 | Q1 . . 2024 | Q4 --====end Write your query in the comments. #sql #tsql #python #pyspark #dataengineer #apachespark #hadoop #faang #us #usa #uk #singapore #it #software #snowflake #databricks #aws #azure #jobs #openings #recruitment #opentowork #collab #prepacement #jobs

101

20 Comments

Create: Used to create a new database or table. Drop: Used to delete an existing database or table. Truncate: Used to delete information in the table but doesn't delete the table itself. Backup: Used to create a backup of an existing database. Insert: Used to insert new rows of data into a table. Delete: Used to delete rows from a table. Update: Used to modify existing records in a table. Select: Used to select data from a table. Union, Intersect, Except: Used to combine the results of two or more select statements. In: Used to compare a value with a set of values. #Sql #Datascience

7

I have completed week 9 of the ultimate big data program and thanks for Sumit Mittal and TrendyTech teams supporting us. ❇ Apache Spark Internals ✅ DataFrame​ Writer API ✔ Write Modes ::: a. overwrite b. ignore c. append d. errorIfExists ✔ partitionBy Clause ✔ bucketBy Clause ✅ Learn more Databricks Community Edition 📒 Spark Internals ::: ✒ Disabling Dynamic Executor Allocation 1. Number of Executor / Containers 2. Number of CPU Cores per Executor 3. Amount of Memory per Executor 📝 spark-submit spark-submit \ --master yarn \ --num-executors 1 \ --executor-cores 1 \ --executor-momory 1G \ --conf spark.dynamicAllocation.enabled=false \ prog1.py ✒ Partition Size == min(maxPartitionBytes,file-size/defaultParallelism) ✒ Non-splittable Vs splittable #BigData #DataEngineer #Spark #SQL #Trendytech

24

2 Comments

Alert : Interview Question ⚠️ ⚠️ - Big Data Hey Big Data Engineers and scala programmers !! 👋 👋 In my previous post I have asked if "+" in **1 + 2 = 3** an OPERATOR or a METHOD / FUNCTION . ->>>>ANSWER<<<<-: =>OPTION - 2 In scala, there is no concept of operators for + , - , ÷ , × ((addition, subtraction, division or multiplication)). In scala, ' + ' , ' - ' , ' ÷ ' , ' × ' appear as operators but they are actually implemented as "method" in scala. so, the " + " is not an **operator** but a **method** in scala.!! #bigdata #scala #programming #dataengineer #dataengineering #interview #learning

10

Class me Postgres SQL chal riya hai but apun abhi LeetCode par Crack SQL Interview in 50 Qs kar riya hai 😂 Harkirat Singh 100xDevs #SQL #Postgres #Development

18

Today participated in #biweekly contest on #leetcode I solved 3 out of 4 problems and I tried for 4th one but I unable to solve it during #contest. I got 1632 #Rank out of 25000+ participates. Problem 1 :- Check if Grid Satisfies Conditions In this problem, you're given a grid, and you need to determine whether it satisfies certain conditions. To solve this, you'll iterate through each cell in the grid, checking if each one meets the specified conditions. If all cells meet the conditions, you return True; otherwise, you return False. Problem 2 : - Maximum Points Inside the Square Here, the task is to find the maximum number of points that can fit inside a square. To approach this, you'll calculate the maximum side length of the square that can contain each point. Then, you'll sort the points based on this side length in ascending order. By iterating through the sorted points and counting the number of points that fit inside each square, you'll determine the maximum count of points that can be accommodated. Problem 3 :- Minimum Substring Partition of Equal Character Frequency In this problem, you're asked to partition a given string into substrings such that each substring has equal frequencies of characters. You can achieve this using dynamic programming. Define a function, dp[i], as the minimum number of substrings needed to partition the first i characters of the string. Iterate through the string, updating dp[i] based on the minimum of dp[L-1] + 1 for all L such that s[L:i] forms a balanced string. By maintaining a prefix frequency table to check the balance of substrings, you'll be able to find the minimum number of substrings required for the given string. #leetcode #contest #biweekly #weekly #coding #consistency #happycoding 🥰🥰

41

1 Comment

😊 Day-37 of #UnexpectedDayOfDS_Journey. Topic Covered (SQL) : ➡ SQL Clauses (GROUP BY, HAVING, ORDER BY). Problem that I solved today: (CodeChef - 4 ) ▶ Handling NULL Values ▶ Salary of Employees ▶ Department of Each Employee ▶ Corporate Salary Trends (LeetCode - 2) ▶ 1693. Daily Leads and Partners ▶ 586. Customer Placing the Largest Number of Orders Problems Link: #DataScience

3

Here’s a list of the most common Spark interview questions that every fresher should know before walking into that interview room 👇🏻 It is purely based on Shashank Mishra 🇮🇳 experiences of giving & taking interviews 🙂 ✅ What is the use of SparkContext? ✅ What does the Spark driver do? ✅ What is RDD? ✅ What is lazy evaluation in Spark? ✅ Difference between DataFrame & Dataset? ✅ Difference between Job, Stage, and Task? ✅ How do you check for data skewness and how to fix it? ✅ Difference between Repartition and Coalesce? ✅ Reasons for the driver to go out of memory? ✅ What could be the possible reasons for an executor to go out of memory and how to fix it? ✅ Explain memory allocation for a spark job? ✅ Explain the architecture of Yarn allocating resources for a spark job? ✅ Difference between broadcast and accumulator? ✅ Difference between cache and persist? ✅ How does sorted merge join work in Spark? Cheers - Grow Data Skills Shashank Mishra 🇮🇳 SHAILJA MISHRA🟢 Sahil Choudhary 🙂 ⏳ 2 days left for GrowDataSkills "Low Code SQL Driven ETL With DBT" Bootcamp to begin ⤵️    🚨 Only 30 spots are left! Use code DBT200 and grab your spot in this amazing bootcamp 🚨    👉🏻 Enroll Here - https://lnkd.in/df5fiEEp 📅 Live classes kick off on 11-May-2024 🚨 We are conducting a FREE Workshop "How to prepare for Data Engineering interviews in 2024?" for all aspiring data engineers 🙂 Will share all experience of cracking top product based companies 👇🏻 🔗 Register here for FREE - https://lnkd.in/d6vfXcgF 📅 Date & Time: 11-May-2024, from 10:30 AM to 12:00 PM IST #dataengineer #interview #interviewexperience #spark

43

2 Comments

#SQL questions asked in top product-based ( Amazon, Walmart Global Tech India , Paytm ) companies:- 🚀 ✨Question 1: You have a table named “FlightBookings” with columns: BookingID, FlightDate, PassengerID, Destination. Write a SQL query to determine which destination has seen a steady month-on-month increase in bookings over the last year. ✨Question 2: You have three tables: "Employees," "Departments," and "Salaries." The "Employees" table has the following columns: EmployeeID, EmployeeName, DepartmentID. The "Departments" table has the following columns: DepartmentID, DepartmentName. The "Salaries" table has the following columns: EmployeeID, Salary, EffectiveDate. Write a SQL query to retrieve the employee who has had the highest salary increase within the last year, along with their name, department, and the percentage increase. ✨Question 3: Consider a table named “TouristSpots” with columns: SpotID, SpotName, VisitorID, VisitDate. Write a SQL query to find the least visited tourist spots in the last summer. ✨Question 4: There are two tables: “BlogPosts” and “Comments”. The “BlogPosts” table has columns: PostID, Title, PostDate, AuthorID. The “Comments” table has columns: CommentID, PostID, CommentDate, Text. Write a SQL query to fetch the blog posts that have not received any comments within a week of their posting. ✨Question 5: Consider a table named “OnlineCourses” with columns: CourseID, EnrollmentDate, StudentID, CompletionDate. Write a SQL query to determine the courses which have the highest drop rate (i.e., students enrolling but not completing). ✨Question 6: Consider a table named “Elections” with columns: CandidateID, VoterID, VoteDate. Write an SQL query to calculate the candidate who received the highest number of votes each month. Follow Anchal Sharma for mire such posts😄 Credit : Chaitanya Kaul #sql #sqlnotes #google #collab #amazon

100

11 Comments

Top SQL interview questions

1

My latest project : IPL Data Analysis with Apache Spark! In this project, I've built an end-to-end data pipeline to analyze IPL data, demonstrating my skills in Apache Spark. The main focus is on writing efficient Spark code and utilizing various transformation functions. GitHub Source Code: https://lnkd.in/g-yHGjix Dataset Link: https://lnkd.in/gYWx4WY2... #DataEngineering #ApacheSpark #IPLAnalysis #BigData #DataPipeline #DataTransformation #GitHub #MachineLearning #DataScience #Analytics Looking forward to your thoughts and feedback!

19

1 Comment

Hello, Connections! 𝐀𝐏𝐀𝐂𝐇𝐄 𝐒𝐏𝐀𝐑𝐊 𝐈𝐍𝐓𝐄𝐑𝐕𝐈𝐄𝐖 𝐐𝐔𝐄𝐒𝐓𝐈𝐎𝐍𝐒 𝐅𝐎𝐑 𝐃𝐀𝐓𝐀 𝐄𝐍𝐆𝐈𝐍𝐄𝐄𝐑𝐒 Preparing for an interview for a data engineer role that involves Apache Spark can be crucial for securing the job. 🔴 𝐁𝐚𝐬𝐢𝐜 𝐂𝐨𝐧𝐜𝐞𝐩𝐭𝐬:-  1- What is Apache Spark, and how does it differ from Hadoop MapReduce? 2- Explain the concept of RDD (Resilient Distributed Dataset) in Spark. 3- How can you create an RDD in Spark? Describe at least two methods. 4- What is the difference between a transformation and an action in Spark? 5- What is Spark's lazy evaluation, and why is it beneficial? 🔴 𝐀𝐫𝐜𝐡𝐢𝐭𝐞𝐜𝐭𝐮𝐫𝐞 𝐚𝐧𝐝 𝐄𝐱𝐞𝐜𝐮𝐭𝐢𝐨𝐧:- 1- Explain the concept of a Directed Acyclic Graph (DAG) in Spark and its role in job execution. 2- What is a Spark executor, and what are its responsibilities? 3- How does the Spark Driver communicate with Spark Executors? 4- What is a SparkContext, and how is it used in a Spark application? 5- What is a SparkSession, and how is it different from SparkContext? 🔴 𝐃𝐚𝐭𝐚 𝐇𝐚𝐧𝐝𝐥𝐢𝐧𝐠 𝐚𝐧𝐝 𝐎𝐩𝐞𝐫𝐚𝐭𝐢𝐨𝐧𝐬:- 1- How does Spark handle memory management? Describe the division between execution and storage memory. 2- What are broadcast variables and accumulators in Spark? How are they used? 3- How does Spark ensure fault tolerance? Describe the role of lineage and checkpointing. 4- Explain the difference between narrow and wide transformations. Provide examples. 5- What is the difference between map and flatMap in Spark? 🔴 𝐒𝐩𝐚𝐫𝐤 𝐒𝐐𝐋 𝐚𝐧𝐝 𝐃𝐚𝐭𝐚𝐅𝐫𝐚𝐦𝐞𝐬:- 1- What is Spark SQL, and how does it integrate with the Spark ecosystem? 2- What is a DataFrame in Spark, and how does it differ from an RDD? 3- How do you create a DataFrame in Spark from a JSON file? 4- What is the Catalyst optimizer, and how does it improve the performance of Spark SQL? 5- Explain the concept of a DataFrame API and its benefits over RDDs. 🔴 𝐀𝐝𝐯𝐚𝐧𝐜𝐞𝐝 𝐂𝐨𝐧𝐜𝐞𝐩𝐭𝐬:- 1 - What is Apache Spark Streaming, and how does it handle real-time data processing? 2- What is the difference between Spark Streaming and Structured Streaming? 3- How do you handle schema evolution in Spark? 4- What is a partition in Spark, and why is it important? 5- How can you optimize Spark jobs for better performance? ⏩ 𝐉𝐨𝐢𝐧 𝐦𝐲 𝐓𝐞𝐥𝐞𝐠𝐫𝐚𝐦 𝐂𝐡𝐚𝐧𝐧𝐞𝐥 𝐭𝐨 𝐥𝐞𝐚𝐫𝐧 𝐃𝐚𝐭𝐚 𝐒𝐜𝐢𝐞𝐧𝐜𝐞 & 𝐀𝐧𝐚𝐥𝐲𝐭𝐢𝐜𝐬: https://t.me/LK_Data_world ⏩ If you found this post informative, save and repost it🔁. ⏩ Follow for more Lovee Kumar 🛎 #data #dataengineer #bigdataengineer #datascience #spark #pyspark #interviewpreparation #skills #guide #interview #linkedinconnection

158

16 Comments

Hi everyone 😊 , Hope everyone's doing great. Today I'm sharing an SQL Interview question based on the Running Total. I've solved the question with two methods (1) Using window function (2) Using self join. Please Share your golden words so that i can improve my skills. 🙏 DDL and DML for the above question: DROP TABLE IF EXISTS Sales ; CREATE TABLE Sales (   Date VARCHAR(512),   sales INT ); INSERT INTO Sales (Date, sales) VALUES ('01-01-2024', '1000'); INSERT INTO Sales (Date, sales) VALUES ('02-01-2024', '8000'); INSERT INTO Sales (Date, sales) VALUES ('03-01-2024', '11000'); INSERT INTO Sales (Date, sales) VALUES ('04-01-2024', '11500'); INSERT INTO Sales (Date, sales) VALUES ('05-01-2024', '13000'); INSERT INTO Sales (Date, sales) VALUES ('06-01-2024', '12400'); INSERT INTO Sales (Date, sales) VALUES ('07-01-2024', '11500'); INSERT INTO Sales (Date, sales) VALUES ('08-01-2024', '16000'); INSERT INTO Sales (Date, sales) VALUES ('09-01-2024', '17050'); INSERT INTO Sales (Date, sales) VALUES ('10-01-2024', '18050'); #SQL #DataAnalytics #MySQL

51

Hi guys I come up with a good interview question regarding issue appears after joining spark data frames why problem will occur after joining two data frames while selecting common column from both tables (error: Ambiguity), but nothing will happen when we use select ( '*')? let's take a product tables and orders table both tables contain common column name as "product_quantity","product_id" join both table by using "product_id" ## join and selecting required columns Order_df.join(product_df,order_df.prod_id=product_df.prod_id,’inner’) \ . select(‘order_id’,’prod_name’,’unit_price’,’product_quan’) \ . sort (‘order_id’ () # this piece of code will through a error as ambiguity error ## join and select all columns Order_df.join(product_df,order_df.prod_id=product_df.prod_id,’inner’) \ . select (‘*’) \ . sort(‘order_id’). show () EXPLANATION: Why do we have this select * working perfectly fine, but Spark complaining about ambiguity when we explicitly refer a column name. Every Dataframe column has a unique ID in the catalog, and the Spark engine always works using those internal ids. These ids are not shown to us, and we are expected to work with the column names. However, the spark engine will translate these column names to ids during the analysis phase. When I refer the column name in my expression, then the Spark engine will internally translate the column names to the column id. And that's where it complains about ambiguity. But when we are using select *, it takes all the column ids and shows them. Translation from column names to column id is not needed. SOLUTION: 1)how to resolve this issue is just rename ambiguity column and apply join # deals with quantity col # solution one for ambiguity by rename column before use it Product_df.withColumn(‘product_quan’,’quant’) Order_df.join(product_df,order_df.prod_id=product_df.prod_id,’inner’) \ . select(‘order_id’,,’prod_name’,’unit_price’,’product_quan’) \ . sort(‘order_id’). show() 2)Just drop one column after join and select one column in select statement # deals with pro_id column # second solution for ambiguity drop any one column after join Order_df.join(product_df,order_df.prod_id=product_df.prod_id,’inner’)\ .drop(product_df.prod_id)\ . select(‘order_id’,’pro_id’,’prod_name’,’unit_price’) \ . sort(‘order_id’). show ()

3