SQL Interview question

Accenture

1. Difference between UNION and UNION ALL and which one is faster?

2. What is CROSS Join and explain the scenario where you have used it?

3. What is Self Join, and can you write a query to extract the manager's name for each employee?

4. Have you implemented CTE (Common Table Expressions)? If yes, could you provide an example?

5. Explain WINDOW FUNCTIONS such as DENSE_RANK(), RANK(), and ROW_NUMBER().

Top product base company

Question 1:

Imagine a table named "Movies" with columns: MovieID, Title, ReleaseDate, GenreID. There's another table "Genres" with columns: GenreID, GenreName. Write a SQL query to fetch the genres that don't have any movies associated with them.

Question 2:

You are given a table named "Attendance" with columns: StudentID, ClassDate, IsPresent (a boolean where 1 indicates presence and 0 indicates absence). Write a SQL query to identify students who have missed more than 3 consecutive classes.

Question 3:

Consider a table named "Elections" with columns: CandidateID, VoterID, VoteDate. Write a SQL query to calculate the candidate who received the highest number of votes each month.

Question 4:

You have a table named "ProductSales" with columns: ProductID, SaleDate, UnitsSold. Write a SQL query to find the top 3 products that have shown the most significant sales growth month-over-month.

Question 5:

You are provided with a table named "LibraryBooks" with columns: BookID, BorrowerID, BorrowDate, ReturnDate. Write a SQL query to find out which books are currently borrowed and have passed their return date without being returned.

Question 6:

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 7:

You have a table named "EmployeeFeedback" with columns: EmployeeID, FeedbackDate, Rating (from 1 to 10). Write a SQL query to identify employees whose rating has been declining for the past 3 consecutive feedbacks.

Question 8:

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 9:

You are given a table named "Subscription" with columns: UserID, SubscriptionDate, ExpiryDate. Write a SQL query to count the number of active subscriptions on the first day of each month in the past year.

Question 10:

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 11:

There are two tables: "Books" and "Authors". The "Books" table has columns: BookID, BookName, AuthorID, SoldCopies. The "Authors" table has columns: AuthorID, AuthorName. Write a SQL query to find authors whose books, on average, have sold more than 10,000 copies, but have written less than 3 books.

Question 12:

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.

EY

1. Difference Between SQL and PL/SQL

2. What Is a Stored Procedure?

3. How Do You Create or Create a Small Stored Procedure?

4. Have You Worked on Functions?

5. Difference Between Stored Procedures and Functions

6.Views with Examples

7. If Any Changes Happen in the Main Tables Used in the Views, Will the View Update Automatically?

8. Is Insertion, Updating, or Deletion Possible Directly on the View?

9. What Are Indexes, and Why Are They Required?

10. Types of Indexes

11. Difference Between Clustered and Non-Clustered Index.

Medium level question asked in Airbnb interview.

Question :

The Airbnb marketing analytics team is trying to understand what are the most common marketing channels that lead users to book their first rental on Airbnb.

Write a query to find the top marketing channel for first booking and percentage of first rental bookings from the aforementioned marketing channel. Round the percentage to the closest integer. Assume there are no ties.

Assumptions:

Marketing channel with null values should be incorporated in the percentage of first bookings calculation, but the top channel should not be a null value.

Meaning, we cannot have null as the top marketing channel.

To avoid integer division, multiple the percentage with 100.0 and not 100.

Tables :

-- bookings : booking_id, user_id, booking_date

-- booking_attribution : booking_id, channel

------------------------------------------------------------------

Solution :

-- Join both the tables on booking_id; rank the bookings of each user in ascending order of booking date. Put this into a CTE (cte_1).

-- Count total user ids for each channel where channel is not Null and rank is 1 according to cte_1 and select the top result and put it into another CTE (cte_2).

--This will give us total first booking count for the channels which were used for first booking.

-- Divide first booking count to total no. of first bookings (total count of unique users) to get desired result.