Implementation Internship – Assignment.
Welcome to Xeno!
💙
As part of your interview process, we’d love to get a sense of how you think about data,
integrations, and information workflows. This short assignment is designed to assess your
understanding of key concepts you’ll encounter on the job - especially those related to SQL and
data readiness.
This is not a coding test, it’s all about your approach, clarity of thought, and how well you
understand the basics.
P.S. You’re already one step ahead of many applicants, we’re rooting for you. 🥳
Objective: Understand Your Thought Process on Client Data Handling
Prerequisite: Install MySQL and upload the data given in the attached file (Set the name of the
table to be ‘customers’)
🧠 Assignment Tasks
1. SQL & Data Familiarity
The client has shared a CSV file containing customer data with the following columns:
● Customer ID
● Full Name
● Email
● Phone
● City
● Signup Date
Please find the sheet attached here.
Questions:
1. What steps would you take to review this data before importing it into a system? Please
explain the process in not more than 3 lines.
2. Please execute each of the queries and support your queries with a screenshot of your
results.
Write SQL queries for the following:
● Write a query to display all customers from the city 'Delhi'.
● Count the number of signups in the last 30 days. Assume today to be 16th April
2025
● List unique cities where customers are based
● List the top 3 cities by number of signups.
● Assume there’s another table orders (customer_id, order_id, amount). How
would you find customers who have never placed an order?
2. Data Transformation & Enrichment
The operations teams want a few more details to ensure smooth operations.
Questions:
● Add a new column to show if the email domain is ‘[Link]’ or not. Fill it with ‘Yes’ or
‘No.
● Extract the first name from the name column and store it in ‘first_name’
● Add a column ‘signup_month’ to capture the month name the customer signed up.
● Create a report which shows the no. of GMAIL customers who have signed up for each
day of the week.
● Create a new table ‘vip_customers’ with customers from Delhi, Mumbai and Bangalore
who have signed up in the last 60 days from 16th April 2025.
3. Analytics & Reporting
The business team wants insights from the customer data.
Questions:
Write SQL queries for the following:
● Show a monthly signup count for the past 6 months.
● Get a list of cities with more than 20 customers.
● Find the date with the highest number of signups.
● Add a new column to show the day of the signup date. Find the day with the highest
number of signups.
If qualified for the next round, please keep the MySQL server ready.
Note - Please avoid using AI tools to solve all the challenges and practical questions. If you do
rely on AI for any part of the research, kindly mention the source and clarify for what it was
used.