Dataford

SQL Query Breakdown: Time Spent Sending vs. Opening Snaps by Age Group Recently, I worked on an interesting analysis to breakdown the time spent sending vs. opening snaps across different age groups. The goal was to determine what percentage of the total time spent is dedicated to each activity within these age buckets. Problem: Given a dataset of user activities, I wanted to find the proportion of time each age group spends on sending and opening snaps, as a percentage of the total time spent on these two activities. Thought Process: Data Aggregation: I started by grouping the data by the age_bucket from the age_breakdown table, as we needed the age groups for each user. Activity Filtering: I filtered the activities to focus only on two types: sending and opening snaps. Using the CASE statements allowed me to sum the time_spent for each activity type. Percentage Calculation: I calculated the percentage of time spent sending and opening by dividing the individual activity time by the total time spent on both activities for each age group. Rounding: I used ROUND() to ensure the percentages are displayed with two decimal places for clearer interpretation. Why I Used LEFT JOIN: I opted for a LEFT JOIN between the activities and age_breakdown tables to ensure that all users’ activities are included in the analysis, even if some users might not have an age bucket assigned. This guarantees that the data is as comprehensive as possible while preserving missing values where necessary. You can find the question on Dataford. link: https://lnkd.in/gNRnpkVT How do you approach calculating percentages in SQL? Share your thoughts below! Shane Butler , Venkata Naga Sai Kumar Bysani, Abhisek Sahu, Prithvi S., Abhinav Singh #SQL #DataAnalysis #DataScience #Analytics #DataDriven #BusinessIntelligence

This SQL Interview question asked in IBM IBM is developing a new feature to analyze user purchasing behavior for all Fridays in the first quarter of the year. For each Friday separately, calculate the average amount users have spent per order. The output should include the week number of that Friday and the average amount spent, rounded to two decimal places. 📃 user_purchases table: Column Name | Description user_id | Identifier of user date | Date of purchase amount_spent | Amount spent day_name | Name of the day Question credit : Dataford

This SQL question asked in Amazon 🚚 You have a transactional dataset from Amazon, detailing sales across various products and marketplaces. Your objective is to identify and list the top 3 sellers in each product category for the month of January. sales_data table: Column Name | Description seller_id | Identifier of seller total_sales | Total sales product_category | Category of product market_place | Market place month | Month Question credit : Dataford

Morning LinkedIn fam! 👋 I recently tackled an interesting SQL challenge that required finding users who have logged in for at least 10 consecutive days from a table named user_login. I wanted to share my thought process and approach in simple terms for all the aspiring data analysts out there! 🚀 Problem Recap: We have a table user_login with the following columns: • user_id: Unique identifier for each user • login_date: The date when the user logged in The task was to identify users who have logged in at least 10 days in a row. For each such occurrence, we need to display the user_id and the first date of these 10 consecutive days. My Thought Process: 1. Understand the Problem Requirements: The first step was to understand the requirement for consecutive days. If a user logs in on 1st, 2nd, 3rd,… up to the 10th without skipping any day, it’s a valid sequence. 2. Identify Consecutive Days: My idea was to create a unique number for each row that represents the difference between the login_date and a sequence number (ROW_NUMBER()) for each user. This way, dates that are exactly 1 day apart will maintain the same difference. 3. Grouping Dates: Using this difference, I could group together consecutive dates and check if the count of these dates is 10 or more. If the count was 10 or more, it meant we had a sequence of 10 consecutive days. 4. Getting the First Date of Each Sequence: For each valid group, I could then fetch the earliest date, which represents the start of the sequence. What I Learned: • Understanding the Problem: Breaking down the problem into smaller steps helps in formulating the right solution. • Using Window Functions: The power of ROW_NUMBER() and OVER() in SQL is incredible when it comes to finding patterns in data. • Thinking Logically: Focus on the logic and use SQL functions that simplify achieving that logic! Credits: This question is from Dataford. If you’re looking to practice SQL problems and enhance your data skills, check out their website! link to the question: https://lnkd.in/gnE_SFPc I hope this helps someone who might be facing similar challenges! 🚀 If you have any questions or different approaches, I’d love to hear them in the comments. 😊 #SQL #DataAnalysis #DataScience #Learning #CareerGrowth #EntryLevelAnalyst #ProblemSolving #LinkedInLearning

There were times I felt so close to giving up. After hearing 'no' again and again. I wondered if a 'yes' would ever come. But I kept at it, staying consistent and believing that my moment was near. And it was. That 'yes' came when I least expected it. If you’re feeling tired, remember—you’re just one step away from your 'YES.' Keep it up, and you’ll get there! 💯