<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Akshat Agrawal on Medium]]></title>
        <description><![CDATA[Stories by Akshat Agrawal on Medium]]></description>
        <link>https://medium.com/@akshatagrawal9431?source=rss-a244e194dfaf------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*Ky5MM8FNSPPF_36E</url>
            <title>Stories by Akshat Agrawal on Medium</title>
            <link>https://medium.com/@akshatagrawal9431?source=rss-a244e194dfaf------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 08:55:29 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@akshatagrawal9431/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Build a Simple Python Chatbot from Scratch (Beginner-Friendly)]]></title>
            <link>https://medium.com/@akshatagrawal9431/build-a-simple-python-chatbot-from-scratch-beginner-friendly-984f23ed52dd?source=rss-a244e194dfaf------2</link>
            <guid isPermaLink="false">https://medium.com/p/984f23ed52dd</guid>
            <category><![CDATA[chatbots]]></category>
            <category><![CDATA[akshat-agrawal-medium]]></category>
            <category><![CDATA[resume-project-2026]]></category>
            <category><![CDATA[python-projects]]></category>
            <category><![CDATA[chatbot-project]]></category>
            <dc:creator><![CDATA[Akshat Agrawal]]></dc:creator>
            <pubDate>Tue, 30 Dec 2025 04:50:53 GMT</pubDate>
            <atom:updated>2025-12-30T04:55:42.357Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zWkFdciyjGQGiTYfDrwjzw.png" /></figure><p>A <strong>chatbot</strong> is a software program that interacts with users through text or voice by understanding their input and responding intelligently. Chatbots are widely used in <strong>customer support, education, healthcare, banking, and HR</strong> to automate repetitive queries and provide instant assistance 24/7.</p><p>Building a chatbot is a great way to learn <strong>Natural Language Processing (NLP)</strong> and <strong>machine learning</strong>, as it demonstrates how raw text is converted into numerical data and classified to generate meaningful responses. In this project, we build a simple chatbot from scratch using Python to understand these core concepts practically.</p><h3>1. What Are We Building?</h3><p>We are building a <strong>rule-based + machine learning chatbot</strong> using:</p><ul><li><strong>NLTK</strong> for text preprocessing</li><li><strong>Bag of Words (BoW)</strong> for text-to-number conversion</li><li><strong>Neural Network (TensorFlow/Keras)</strong> for intent classification</li></ul><p><strong>Important intuition</strong>:<br>The chatbot does <strong>NOT understand language</strong>.<br>It learns to <strong>map user sentences → intent labels → predefined responses</strong>.</p><h3>2. Chatbot Architecture (Big Picture)</h3><pre>User Input<br>   ↓<br>Text Preprocessing (tokenize + lemmatize)<br>   ↓<br>Bag of Words (numerical vector)<br>   ↓<br>Neural Network (intent classifier)<br>   ↓<br>Predicted Intent<br>   ↓<br>Random Response from intents.json</pre><p>This is the <strong>same core logic</strong> used in early customer-support bots before GPT era.</p><p>Get the code <a href="https://colab.research.google.com/drive/1c4cTEp7osDqlvT3vDWVzPNNMSu7XCi8M?authuser=2#scrollTo=eQdSZYRFczsB">here</a>: <a href="https://colab.research.google.com/drive/1c4cTEp7osDqlvT3vDWVzPNNMSu7XCi8M?authuser=2#scrollTo=eQdSZYRFczsB">https://colab.research.google.com/drive/1c4cTEp7osDqlvT3vDWVzPNNMSu7XCi8M?authuser=2#scrollTo=eQdSZYRFczsB</a></p><h3>3. Dataset: intents.json (The Brain of the Bot)</h3><h3>What is an Intent?</h3><p>An <strong>intent</strong> represents what the user <em>wants</em>.</p><p>Example:</p><pre>{<br>  &quot;tag&quot;: &quot;hello&quot;,<br>  &quot;patterns&quot;: [&quot;Hi&quot;, &quot;Hello&quot;, &quot;Good morning&quot;],<br>  &quot;responses&quot;: [&quot;Hey!&quot;, &quot;Hello there!&quot;]<br>}</pre><ul><li><strong>patterns</strong> → user questions</li><li><strong>tag</strong> → class label/ output label</li><li><strong>responses</strong> → bot replies</li></ul><p>Think of it as:</p><blockquote><em>“Different ways of asking the same thing”</em></blockquote><h3>4. Text Preprocessing (NLP Basics)</h3><h3>Why preprocessing?</h3><p>Computers don’t understand raw text. So, we:</p><ol><li><strong>Tokenize</strong> → split sentence into words. ‘I am Akshat’ -&gt; [‘I’, ‘am’, ‘Akshat’]</li><li><strong>Lowercase -&gt; ‘Akshat -&gt; ‘akshat’</strong></li><li><strong>Lemmatize</strong> → convert words to base form</li></ol><ul><li>“running” → “run”</li></ul><p>4. <strong>Remove stopwords</strong> like ‘a’, ‘the’, ‘and’, etc to reduce noise.</p><pre>tokens = nltk.word_tokenize(sentence)<br>lemmatizer.lemmatize(word.lower())</pre><p>This reduces noise and improves learning.</p><h3>5. Bag of Words (Text → Numbers)</h3><h3>Definition (Very Important for Students)</h3><p><strong>Bag of Words</strong> converts text into a binary vector based on vocabulary presence.</p><p>Example vocabulary:</p><pre>[&quot;hello&quot;, &quot;how&quot;, &quot;you&quot;]</pre><p>Sentence:</p><pre>&quot;hello you&quot;</pre><p>BoW Vector:</p><pre>[1, 0, 1]</pre><h3>Why BoW?</h3><p>Neural networks <strong>only understand numbers</strong>.</p><p>We could also use word embeddings, TF-IDF.</p><h3>6. Preparing Training Data</h3><ul><li>train_X → BoW vectors</li><li>train_Y → One-hot encoded intent labels</li></ul><p>Example:</p><pre>hello → [1, 0, 0, 0]<br>goodbye → [0, 1, 0, 0]</pre><p>This turns chatbot learning into a <strong>multi-class classification problem</strong>.</p><h3>7. Neural Network Model</h3><p>Understand the neural network working better- <a href="https://claude.ai/share/2a4bebba-e565-480f-9837-d337317a48cd">Link</a></p><h3>Model Design (Simple but Effective)</h3><pre>Dense(128) → Dropout<br>Dense(64)  → Dropout<br>Dense(output_size, softmax)</pre><h3>Why this works?</h3><ul><li><strong>ReLU</strong> → learns non-linear patterns</li><li><strong>Dropout</strong> → prevents overfitting</li><li><strong>Softmax</strong> → gives probability for each intent</li></ul><p>Loss:</p><pre>categorical_crossentropy</pre><p>Optimizer:</p><pre>Adam</pre><p>This is the <strong>same setup used in many production intent classifiers</strong>.</p><h3>8. Training the Model</h3><pre>model.fit(train_X, train_Y, epochs=150)</pre><ul><li>Small dataset → more epochs</li><li>Accuracy improves steadily</li><li>Overfitting controlled using Dropout</li></ul><h3>9. Predicting User Intent</h3><h3>Step-by-step:</h3><ol><li>Clean user input</li><li>Convert to BoW</li><li>Predict probabilities</li><li>Select intent above threshold (0.5)</li></ol><pre>model.predict()</pre><p>If confidence is low → fallback response:</p><pre>&quot;Sorry! I don&#39;t understand.&quot;</pre><h3>10. Generating the Final Response</h3><p>Once intent is detected:</p><ul><li>Pick a <strong>random response</strong> from that intent</li><li>Makes the chatbot feel <strong>less robotic</strong></li></ul><h3>11. Chatbot in Action</h3><pre>User: Hi<br>Bot: Good morning!</pre><pre>User: How are you?<br>Bot: Actually, I&#39;m okay and you?</pre><pre>User: Bye<br>Bot: Goodbye!</pre><h3>12. Real-World Use Cases</h3><p>This exact architecture is used for frequently asked repeated questions in:</p><ul><li>FAQ bots</li><li>Customer support chatbots</li><li>College / training institute bots</li><li>HR onboarding bots</li></ul><p><strong>Industry upgrade path</strong>:</p><ul><li>Replace BoW → <strong>Word Embeddings, TF-IDF</strong></li><li>Replace Dense NN → <strong>LSTM / Transformers</strong></li></ul><h3>13. Key Learning Takeaways</h3><ul><li>Chatbots are <strong>classification systems</strong>, not magic</li><li>NLP = preprocessing + numerical representation</li><li>Even simple models can give powerful results</li><li>Strong foundation before jumping to ChatGPT APIs</li></ul><p>In or next project, we’ll improve these chatbots using Langchain, memory and LLMs.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=984f23ed52dd" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Recruiters Don’t Read Resumes. They Scan Them.]]></title>
            <link>https://medium.com/@akshatagrawal9431/recruiters-dont-read-resumes-they-scan-them-1e416c799f79?source=rss-a244e194dfaf------2</link>
            <guid isPermaLink="false">https://medium.com/p/1e416c799f79</guid>
            <category><![CDATA[resume-writing]]></category>
            <category><![CDATA[resume]]></category>
            <category><![CDATA[resume-tips]]></category>
            <category><![CDATA[akshat-agrawal]]></category>
            <dc:creator><![CDATA[Akshat Agrawal]]></dc:creator>
            <pubDate>Sun, 14 Dec 2025 16:31:20 GMT</pubDate>
            <atom:updated>2025-12-14T16:31:20.787Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/645/1*xMR623QtNpOnC0ZkEAm_Cw.png" /></figure><p>Most professionals believe their resume is <em>read carefully</em>.<br>It isn’t.</p><p>In most US hiring pipelines, resumes are <strong>scanned for 10–15 seconds</strong> before a decision is made to move forward or reject.</p><p>That single reality should change how you write your resume.</p><h3>What Recruiters Are Actually Trying to Do</h3><p>Recruiters are not trying to understand your entire career.</p><p>They are trying to answer one question as fast as possible:</p><blockquote><strong><em>“Is this person worth a deeper look?”</em></strong></blockquote><p>With hundreds of resumes to review, efficiency matters more than detail.</p><h3>How Resumes Are Scanned in the First 10 Seconds</h3><p>In those first few seconds, attention usually goes in this order:</p><ol><li><strong>Company names and job titles</strong></li><li><strong>Most recent role</strong></li><li><strong>Keywords related to the job</strong></li><li><strong>Numbers or measurable impact</strong></li></ol><p>Long paragraphs, generic responsibilities, and dense text are often skipped entirely.</p><h3>Why Responsibilities Don’t Work</h3><p>Statements like</p><ul><li>“Responsible for data analysis”</li><li>“Worked on dashboards”</li><li>“Handled reporting tasks”</li></ul><p><strong>Tell the recruiter <em>what you were supposed to do</em>, not what you actually achieved.</strong></p><p>Hiring decisions are driven by <strong>outcomes</strong>, not duties.</p><h3>What Strong Resume Bullets Have in Common</h3><p>Effective resume bullets usually contain three elements:</p><p><strong>Action + Tool + Impact</strong></p><p>For example:</p><ul><li>Weak: “Created dashboards using Tableau”</li><li>Strong: “Created Tableau dashboards for sales teams, reducing manual reporting by <strong>30%</strong>”</li></ul><p>The work may be the same — the perception is completely different.</p><h3>Metrics Matter (Even Small Ones)</h3><p>You don’t need massive numbers.</p><p>Saved time.<br> Improved accuracy.<br> Supported decisions.<br> Reduced manual effort.</p><p>Even approximate metrics are better than none.</p><p>They signal that you understand <strong>business impact</strong>, not just tools.</p><h3>One Simple Action You Can Take Today</h3><p>Don’t rewrite your entire resume yet.</p><p>Instead, pick <strong>one bullet point</strong> and ask:</p><blockquote><strong><em>“What changed because of my work?”</em></strong></blockquote><p>Rewrite just that line with clarity and impact.</p><p>That single improvement often makes recruiters pause — and that pause is everything.</p><p>Get Microsoft official templates <a href="https://word.cloud.microsoft/search/resumes/?wdOrigin=COMMUNITY.WORD.PARTNERS%2CSEO-INTENT.INTERNAL-LANDING-INTENT1.SEARCHTEMPLATES">here</a></p><h3>Final Thought</h3><p>Your resume is not a biography. It’s a <strong>decision document</strong>.</p><p>Design it to be scanned, not read.</p><p>If you do that well, you dramatically increase your chances of getting to the next stage.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1e416c799f79" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[ Building Cleanistan with Lovable.dev: A Hyperlocal Laundry Website in Under an Hour]]></title>
            <link>https://medium.com/@akshatagrawal9431/building-cleanistan-with-lovable-dev-a-hyperlocal-laundry-website-in-under-an-hour-923251a3466c?source=rss-a244e194dfaf------2</link>
            <guid isPermaLink="false">https://medium.com/p/923251a3466c</guid>
            <category><![CDATA[lovableai]]></category>
            <category><![CDATA[web-development-using-ai]]></category>
            <category><![CDATA[website-using-ai]]></category>
            <category><![CDATA[ai-web-development]]></category>
            <category><![CDATA[free-ai-tools]]></category>
            <dc:creator><![CDATA[Akshat Agrawal]]></dc:creator>
            <pubDate>Mon, 14 Jul 2025 18:57:04 GMT</pubDate>
            <atom:updated>2025-07-14T18:57:04.752Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>Akshat Agrawal • July 2025</strong></p><h3>Introduction</h3><p>Launching a professional, laptop and mobile-friendly website for your business used to mean weeks of work or hiring a dev team. With <a href="https://lovable.dev/">Lovable</a>, you can now build a complete, user-centric, responsive site <em>just by chatting</em> with an AI. From a long time, I was planning to create a landing page but due to less technical knowledge and budget, couldn’t get it done.</p><p>But wait!!</p><p>I recently used it to create <a href="https://cleanistan.lovable.app/"><strong>Cleanistan</strong></a>, a one-page web app for my local laundry &amp; dry cleaning business in Dehradun, Uttarakhand. It highlights our services, subscription plans, and includes a real-time pickup scheduling form — with <strong>backend data collection powered by Supabase</strong>!</p><p>In this blog, I’ll walk through how I used Lovable to design and ship this web app in under an hour — along with smart integrations and future upgrades you can try too.</p><p>In this webpage, front end and backend both are built. So let’s begin!</p><h3>Motivation</h3><p>I needed a digital front for <strong>Cleanistan</strong> — something functional, beautiful, and conversion-ready. My main goals:</p><ul><li>Clearly list services &amp; pricing</li><li>Let users schedule free pickups</li><li>Collect form submissions into a database</li><li>Tell users about our services</li><li>Enable future add-ons like payments or WhatsApp (to be done next)</li></ul><h4><strong>Lovable</strong> is an AI-powered website builder that helps you create beautiful, professional websites just by chatting with it — like you’re texting a designer.</h4><p>You don’t need to know any coding or design. Simply tell it what kind of website you want, and Lovable will build it for you — sections, buttons, forms, and everything in between.</p><p>It’s perfect for:</p><ul><li>Small business owners</li><li>Freelancers</li><li>Creators</li><li>Startups</li><li>Anyone who wants a quick and powerful website</li></ul><p>Whether you want a landing page, a personal portfolio, or a full business site with forms and integrations — Lovable makes it <strong>fast, easy, and fun</strong>.</p><p>Lovable let me do all this — and more — without touching a single line of front-end or backend code.</p><h3>1. The Hero Section</h3><p><strong>Prompt:</strong><em>“Create a hero section for Cleanistan: brand name, big headline, subtext, background image of laundry, and two CTAs — Schedule Pickup and View Pricing.”</em></p><ul><li>Brand logo and name at top</li><li>Bold headline: <em>“Premium Laundry Service in Dehradun”</em></li><li>Subheadline that communicates convenience</li><li>CTA buttons with scroll-to-section functionality</li><li>Background image overlay of fresh laundry</li></ul><p>Fully mobile responsive right out of the box.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ja1yWPHvpDZwDnsbU5nEoQ.png" /><figcaption>Fig 1: Output of the Prompt 1</figcaption></figure><h3>2. The “Why Us” Section</h3><p><strong>Prompt:</strong><em>“Add a section with four icons and benefits: Quick Turnaround, Free Pickup &amp; Delivery, Premium Quality, and Professional Service.”</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iMnEge7tX-Ny4q4PjCbx-g.png" /><figcaption>Fig 2: Output of the prompt 2</figcaption></figure><h3>3. Services Section</h3><p><strong>Prompt: </strong><em>“Add a services grid: Wash &amp; Iron, Dry Cleaning, Shoe Restoration, Sofa Cleaning, Carpet &amp; Curtain Cleaning, Commercial Laundry.”</em></p><p>Each service card includes:</p><ul><li>Emoji/icon</li><li>Short description</li><li>Price range</li><li>Grid auto-adapts to screen size</li></ul><p>Lovable made it easy to tweak the card layout or copy just by editing my prompt.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iRS3MtBVJ7QeYx3VPlCyGQ.png" /><figcaption>Fig 3: Output of the prompt 3</figcaption></figure><h3>4. Subscription Pricing Plans</h3><p><strong>Prompt:</strong><em>“Add three pricing cards — Basic ₹750, Standard ₹1400, Premium ₹2000. List features and mark the most popular one.”</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ig7iQjAUQaqRiXJEn3Sv6Q.png" /><figcaption>Fig 4: Output of the Prompt 4</figcaption></figure><h3>5. Pickup Scheduling Form</h3><p><strong>Prompt:</strong><em>“Add a pickup form with name, phone, email, address, preferred pickup time/date.”</em></p><p>I also asked it to integrate with <strong>Supabase </strong>so that the backend is also there and we can get the data of the users who schedules a pickup.</p><p>Lovable created a working form with:</p><ul><li>Front-end validation</li><li>Clean layout</li><li>Submit button functionality</li><li>Smooth scroll + animation</li><li>Backend Integration</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/478/1*nTUwbq1XKd4TOHg4WKTh6A.png" /><figcaption>Fig 5: Output of the Prompt 5</figcaption></figure><h3>6. Backend Integration with Supabase + GitHub</h3><p>Lovable lets you <strong>connect the form directly to a backend database</strong> — without writing a single line of backend code.</p><h3>🔄 How I Did It:</h3><ol><li><strong>Connected Supabase</strong> project (via Lovable backend setup UI)</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kgoGVfbEqhp6a3DQDBfJYw.png" /><figcaption>Fig 6.0: Output of the data stored in the backend table while testing.</figcaption></figure><ol><li><strong>Form data</strong> is stored in a real-time Supabase table named pickup_requests</li><li>Every submission is logged and accessible via Supabase dashboard</li><li>GitHub is used to <strong>version control</strong> the app code (Lovable auto-generates this for you) just by asking to do it using the prompts.</li></ol><blockquote><strong><em>Why Supabase?</em></strong><em> It’s free, fast, and handles auth + DB + storage seamlessly. Perfect for MVPs.</em></blockquote><h3><strong>7. Contact Form + Trust Section</strong></h3><p><strong>Prompt:</strong><em>“Add contact form with dropdown for service, message box, and list phone/email.”</em></p><p>Lovable auto-formatted it cleanly and added margin spacing for the right balance between utility and design.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-PVpiD5bP3YC4e67Kw4lfA.png" /><figcaption>Fig 6: Output of the Prompt 7</figcaption></figure><h3>8. Additional Integrations You Can Add (No-Code)</h3><p>Lovable makes it super easy to plug in modern tools:</p><p>Integration Use Case Setup Difficulty <strong>Stripe</strong> Accept online payments for subscriptions Easy <strong>WhatsApp Chat</strong> Instant chat support + lead generation Easy <strong>Calendly</strong> Let users book time slots (pickup windows) Easy <strong>Zapier</strong> Automate data to Google Sheets or Email Easy <strong>Google Analytics / Meta Pixel</strong> Track user behavior Easy <strong>Supabase Auth</strong> Add user login / tracking Intermediate <strong>Airtable</strong> Store form submissions into easy dashboards Easy</p><p>All it takes is a few clicks or embed scripts in the Lovable dashboard.</p><h3>Design Tweaks &amp; Responsiveness</h3><p>With a few prompts, I:</p><ul><li>Customized color themes (Cleanistan uses yellow/purple)</li><li>Enabled smooth scroll navigation</li><li>Applied soft animations on CTA buttons and card hovers</li><li>Ensured full responsiveness from 320px to large screens</li></ul><p>The result: a website that looks like it was built by a pro team, but done in minutes.</p><h3>Technical Summary</h3><p>Feature Tool / Method Used Front-End Sections Prompt-based via Lovable</p><p>Backend DB Supabase (linked via Lovable)</p><p>Version Control GitHub Repo</p><p>Auto-generated Payments (Optional) Stripe Integration</p><p>Data Collection Realtime form-to-DB syncing Contact Forms Built-in with validation</p><p>Mobile Ready Yes</p><h3>🔍 What I Learned</h3><ul><li><strong>Prompt engineering is key</strong>: small changes to wording = huge visual/layout difference. Give good enough detailed prompt to AI and see wonders happening.</li><li><strong>Supabase is amazing</strong>: real-time data logging, clean UI, works beautifully with Lovable</li><li><strong>MVPs don’t need code</strong>: 100% of my website was ready with AI + drag/drop integrations</li><li><strong>Lovable is not just for landing pages</strong> — it’s smart enough to handle multi-section service sites</li></ul><h3>What’s Next</h3><p>Here’s what I plan to build next:</p><ul><li>Take Lovable subscription for a month for 25 USD monthly currently (might update in future)</li><li>Get a custom domain and integrate my website to it.</li><li>Add WhatsApp widget for direct communication</li><li>Create client dashboards using Supabase Auth</li><li>Setup payment checkout via Stripe</li><li>Export data to Google Sheets with Zapier</li><li>Launch Instagram ads that link to Cleanistan site</li></ul><h3>Conclusion</h3><p>Building a branded website doesn’t need dev teams from the beginning, hosting plans, or weeks of work. With Lovable + Supabase, you can launch a real service or business idea in an evening and then probably need to take expert advices on security and other features.</p><p>Check out <a href="https://cleanistan.lovable.app/"><strong>Cleanistan</strong></a>, built with <a href="https://lovable.dev/">Lovable</a>, and let me know what you think!</p><h3>🔗 Links</h3><ul><li>🧼 Live site → <a href="https://cleanistan.lovable.app/">cleanistan.lovable.app</a></li><li>🛠️ Build your own → <a href="https://lovable.dev/">lovable.dev</a></li><li>💻 GitHub repo (auto-generated by Lovable): <a href="https://github.com/akshatagrawal1998/cleanistan">https://github.com/akshatagrawal1998/cleanistan</a></li></ul><p><strong>Reach me on </strong><a href="https://www.linkedin.com/in/akshatagrawal3/"><strong>Linkedin </strong></a><strong>for collabs or questions.</strong></p><p><strong>Connect to me at my </strong><a href="https://www.youtube.com/@thedataholics"><strong>Youtube </strong></a><strong>channel for more content on AI and Data Science &amp; Analytics.</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=923251a3466c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Week 3 Solution | 8 week SQL Challenge]]></title>
            <link>https://medium.com/@akshatagrawal9431/week-3-solution-8-week-sql-challenge-322750a905d2?source=rss-a244e194dfaf------2</link>
            <guid isPermaLink="false">https://medium.com/p/322750a905d2</guid>
            <category><![CDATA[8-week-sql-challenge]]></category>
            <category><![CDATA[data-analyst-interview]]></category>
            <category><![CDATA[akshat-agrawal]]></category>
            <category><![CDATA[data-analyst-case-study]]></category>
            <category><![CDATA[sql-project]]></category>
            <dc:creator><![CDATA[Akshat Agrawal]]></dc:creator>
            <pubDate>Thu, 13 Jun 2024 20:40:49 GMT</pubDate>
            <atom:updated>2024-06-13T20:40:49.667Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1HimU-u3bCfI2ZevLRdYDg.png" /></figure><p><a href="https://8weeksqlchallenge.com/case-study-3/"><strong>Case Study #3</strong></a> on <strong>8 week SQL challenge</strong> is an awesome case study with subscription model dataset. It’s very helpful for all data Analyst, business Analyst enthusiasts where we understand the domain, data, problem statements and then write SQL queries. I highly recommend to do this case study and other case studies given on 8 week SQL challenge.</p><p>You can get the case study documentation, data, DDL commands by clicking on the <a href="https://8weeksqlchallenge.com/case-study-3/"><strong>link</strong></a>.</p><p>First read the introduction, understand case study, ER diagram and the data. Then move on to solve the questions.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/698/1*klBB89OXPeFPAPrPsHaKnw.png" /></figure><h3>— Section A solution: Customer Journey</h3><p>Based off the 8 sample customers provided in the sample from the subscriptions table, write a brief description about each</p><p>customer’s onboarding journey.</p><p>Try to keep it as short as possible — you may also want to run some sort of join to make your explanations a bit easier!</p><p>use db_name</p><p>— 8 sample records in the subscriptions table are with customer_id 1,2,11,13,15,16,18,19</p><p>select * from subscriptions where customer_id in (1,2,11,13,15,16,18,19)</p><p>order by customer_id</p><p>select * from subscriptions</p><p>join plans on subscriptions.plan_id = plans.plan_id</p><p>where subscriptions.customer_id in (1,2,11,13,15,16,18,19)</p><p><strong>Observing these 8 customers’ journey</strong></p><p>customer_id 1 has started with the basic free trial on Aug 1, 2020 and has jumped to basic monthly plan (with limited access and only</p><p>streaming) effective from Aug 8, 2020.</p><p>customer_id 2 has started with the basic free trial on Seot 20, 2020 and has jumped to pro annual plan effective from Sept 27, 2020.</p><p>customer_id 11 has started with the basic free trial on Nov 19, 2020 and has churned after it (effective after the end of free plan</p><p>i.e. from Nov 26, 2020) but he decided to cancel the service on Nov 26, 2020 that’s why the start data of plan_id 4 for customer_id 11</p><p>is mentioned as Nov 26, 2020)</p><p>customer_id 13 has started with the basic free trial on Dec 15, 2020 and upgraded to basic monthly plan on Dec 22, 2020 (effective from</p><p>same day)</p><p>Then after 3 months, he renewed the plan on Mar 29, 2021 as annual pro plan.</p><p>customer_id 15 has started with the basic free trial on Mar 17, 2020 and upgraded to annual pro plan on Mar 24, 2020.</p><p>But decided to cancel the subscription on Apr 29, 2020 but since it’ll end at the end of current billing period (Mar 23, 2021 as it was</p><p>an annual plan). Start data for churn plan shows as Apr 29, 2020 coz he decided to cancel on that date.</p><p>customer_id 16 has started with the basic free trial on may 31, 2020 and upgraded to basic monthly plan on June 7, 2020.</p><p>Again on Oct 21, 2020 he upgraded to annual pro plan.</p><p>customer_id 18 has started with the basic free trial on July 6, 2020 and upgraded to pro monthly plan on July 13, 2020.</p><p>customer_id 19 has started with the basic free trial on June 22, 2020 and upgraded to pro monthly plan on June 29, 2020.</p><p>Then upgraded to annual pro plan on Aug 29, 2020</p><p>1. All customers started with a 7-day free trial and upgraded their subscription plan.</p><p>- All customers satisfied with Foodie-Fi content because they always upgraded their free trial account into ‘paid’ membership.</p><p>2. 6 customers’ subscriptions are ongoing, but customers 11 and 15 ended their subscriptions.</p><p>3. Customer 11 didn’t subscribe to a paid membership</p><p>4. Vast majority of the subscribed customers upgraded their plan after a while</p><blockquote><strong>Section A ends here.</strong></blockquote><h3><strong>— Section B. Data Analysis Questions</strong></h3><p>use db_name;</p><p>select count(*) from subscriptions; — 2650 rows</p><p>select * from plans;<br>select top 10* from subscriptions;</p><p><strong>Q1. How many customers has Foodie-Fi ever had?</strong> <br><strong>Solution#1:</strong></p><p>select count(distinct customer_id) as [Number of customers] from subscriptions;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/213/1*4h4Zx1Cn9Wbi9AsQLWiq3g.png" /></figure><p><strong><em>— Insight:- Foodie-Fi has 1,000 unique customers.</em></strong></p><p><strong>Q2. What is the monthly distribution of trial plan start_date values for our dataset <br>Use the start of the month as the group by value</strong></p><p><strong><em>Understanding:</em> They want to see how many customers signed up each month for the trial plan. Basically we can take start date of month as the group by value.</strong></p><p><strong>Solution #2:</strong></p><p>select month(start_date) as [Month], datename(MONTH, start_date) as [Month Name],<br>count(distinct customer_id) as [Count of customers for free trial plan]<br>from subscriptions <br>where plan_id = 0 <br>group by month(start_date), datename(MONTH, start_date)<br>order by month(start_date);</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/389/1*wP19LnXfnkVKC-fuF62W0Q.png" /></figure><p><strong><em>Insight:-<br>We can see that March has the highest number of trial plans, whereas February has the lowest number of trial plans.</em></strong></p><p><strong>Q3. What plan start_date values occur after the year 2020 for our dataset? </strong><br><strong> — Show the breakdown by count of events for each plan_name</strong></p><p>select distinct p.plan_id, p.plan_name, count(customer_id) as count_customers<br>from plans p <br>left join subscriptions s on s.plan_id = p.plan_id <br>where year(start_date) &gt; 2020<br>group by p.plan_id, p.plan_name;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/307/1*-KNlSR8OtcRMibN1mBUT9A.png" /></figure><p><strong><em>Insight:- We can see that after 2020, pro_annual and pro_monthly plans are sold the most. It indicates that people are really <br>liking the services as 2020 was the starting year for the company, so they are satisfied with the services.</em></strong></p><p><strong><em>But from below analysis we see that count of customers in each plan is less in 2021 as compared to 2020.<br>Also, there were 0 customer on trial plan in 2021. <br>Does it mean that there were no new customers in 2021, or did they jump directly on basic monthly or other plans without going through the trial?</em></strong></p><blockquote><strong>— An additional analysis from my end — Let’s compare the customers count in each plan for 2020 and 2021</strong></blockquote><p>select distinct p.plan_id, p.plan_name, <br>count(distinct case when year(start_date) = 2020 then customer_id end) as count_2020,<br>count(distinct case when year(start_date) = 2021 then customer_id end) as count_2021<br>from plans p <br>left join subscriptions s on s.plan_id = p.plan_id <br>where year(start_date) &gt;= 2020<br>group by p.plan_id, p.plan_name;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/352/1*qNEYE_5g_8jZFFa7d_GrBQ.png" /></figure><p><strong>Q4. What is the customer count and percentage of customers who have churned rounded to 1 decimal place</strong></p><p><strong>We can solve this question by counting number of churned customers (with plan_id = 4) and divide it by total number of customers</strong></p><p><strong>Solution #4:</strong></p><p>creating 2 variables to calculate count of churned customers and total customers and then we’ll get the %.</p><p>declare <a href="http://twitter.com/churned">@churned</a> int, <a href="http://twitter.com/total_customers">@total_customers</a> int</p><p>set <a href="http://twitter.com/churned">@churned</a> = (select cast(count(distinct customer_id) as decimal(10,1)) from subscriptions where plan_id = 4);<br>set <a href="http://twitter.com/total_customers">@total_customers</a> = (select count(distinct customer_id) from subscriptions);</p><p>select <a href="http://twitter.com/churned">@churned</a> as [Churned Customers], <a href="http://twitter.com/total_customers">@total_customers</a> as [Total Customers];</p><p>SELECT <a href="http://twitter.com/churned">@churned</a> as [Churned Customers], <a href="http://twitter.com/total_customers">@total_customers</a> as [Total Customers], <br>CAST(<a href="http://twitter.com/churned">@churned</a> / cast(<a href="http://twitter.com/total_customers">@total_customers</a> as decimal(10,1)) *100 AS DECIMAL(10, 1)) AS Churned_Percentage;</p><blockquote>—<strong> An alternate solution (using case expression)</strong></blockquote><p>select count(distinct case when subscriptions.plan_id = 4 then customer_id end) as [Churned Customers],<br>count(distinct customer_id) as [Total Customers],<br>concat( cast (<br> (count(distinct case when subscriptions.plan_id = 4 then customer_id end)/cast(count(distinct customer_id) as decimal(10,1)) *100)<br>as decimal(10,1)) , ‘%’) as ‘Churned Customers %’<br>from plans<br>join subscriptions on subscriptions.plan_id = plans.plan_id</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/416/1*7DMO9n5QnltZYtmETsdouw.png" /></figure><blockquote><strong>— Insights: There are 307 customers who churned, which is 30.7% of Foodie-Fi customer base.</strong></blockquote><p><strong>Q5. How many customers have churned straight after their initial free trial</strong></p><p><strong>what percentage is this rounded to the nearest whole number?</strong></p><p>— customer_id 11 is such a customer — <br> — select * from subscriptions where customer_id = 11 — in(4,6,11,15,21,33,39,48,51)</p><p><strong>Solution #5:</strong></p><p>select distinct customer_id, <br>DATEDIFF(day, min(case when plan_id = 0 then start_date end), min(case when plan_id = 4 then start_date end)) as date_diff_bewteen_trial_and_churn_plan<br>into #t_1<br>from subscriptions <br>group by customer_id order by customer_id</p><p>select * from #t_1 order by customer_id</p><p>declare <a href="http://twitter.com/churned_after_free_t">@churned_after_free_t</a>rial int, <a href="http://twitter.com/total_customers">@total_customers</a> int</p><p><strong><em>— now let’s find no of customers who are churned immediately after basic plan and total number of customers</em></strong></p><p>set <a href="http://twitter.com/churned_after_free_t">@churned_after_free_t</a>rial = (select count(distinct customer_id) from #t_1 where date_diff_bewteen_trial_and_churn_plan = 7);</p><p>set <a href="http://twitter.com/total_customers">@total_customers</a> = (select count(distinct customer_id) from #t_1);<br> — select <a href="http://twitter.com/churned_after_free_t">@churned_after_free_t</a>rial as Churned_Customers_After_free_trial, <a href="http://twitter.com/total_customers">@total_customers</a> as Total_Customers;</p><p>select <a href="http://twitter.com/churned_after_free_t">@churned_after_free_t</a>rial as Churned_Customers, <br>concat(cast((<a href="http://twitter.com/churned_after_free_t">@churned_after_free_t</a>rial*1.0/@total_customers) *100 as decimal(10,0)) , ‘%’) as Churned_Percentage</p><p>— select * from #t_1 where diff_bewteen_start_and_churn_date = 7 order by customer_id;</p><p>drop table #t_1;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/308/1*GuaROc-Vlk245g4oVXodAA.png" /></figure><p><strong><em>— Alternate solution</em></strong><br>/*<br>If a customer is churned immediately after the basic plan, then for the first record i.e. plan_id = 0 row_number = 1 and for the <br>immedicate next record the plan_id will be 4 and row_number = 4, so we can count those records where plan_id = 4 and rn = 2<br>as the customers who churnede immediately after the basic plan <br>*/</p><p>;<br>with cte as (<br>select customer_id, plan_id, row_number() over (partition by customer_id order by start_date) as rn<br>from subscriptions<br>)<br>select — count(distinct customer_id) as [Churned Customers immediately after the basic plan],<br>count(distinct case when plan_id = 4 and rn = 2 then customer_id end) as [Churned Customers immediately after the basic plan],<br>cast((count(distinct case when plan_id = 4 and rn = 2 then customer_id end)/<br>cast(count(distinct customer_id) as decimal(10,0)))*100 as decimal(10,0)) as [Churned %]<br>from cte</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/419/1*-V0gzPIoaA5V8zRHUwUxHw.png" /></figure><p><strong><em>Insight:-</em></strong></p><p><strong><em>— 92 customers churned straight after the initial free trial which is 9% of entire customer base.<br> — out of 1000 customers who have taken trial plan, only 92 did not upgrade to any plan</em></strong></p><p><strong>Q6. What is the number and percentage of customer plans after their initial free trial?</strong></p><p><strong><em>Understanding: Question is asking for number and percentage of customers who converted to becoming paid customer after the trial.</em></strong></p><p><strong><em>Logic Used:</em></strong></p><p><strong><em>Find customer’s next plan which is located in the next row using LEAD(). Run the next_plan_cte separately to <br>view the next plan results and understand how LEAD() works.<br>Filter for plan_id = 0 as every customer has to start from the trial plan at 0.<br>Then we’ll group by next_plan_id and count the customers for each plan_id</em></strong></p><p>;<br>with next_plan_cte as (<br>select customer_id, plan_id, lead(plan_id) over (partition by customer_id order by start_date) as next_plan_id<br>from subscriptions<br>)<br>, cte1 as (<br>select * from next_plan_cte where plan_id = 0<br>)<br> — select * from cte1<br>, cte2 as (<br>select next_plan_id as [Next Plan ID after Basic Trial], count(distinct customer_id) as [Count of customer after basic plan]<br>from cte1<br>group by next_plan_id<br>)<br> — select * from cte2<br>select [Next Plan ID after Basic Trial], [Count of customer after basic plan], <br>cast(([Count of customer after basic plan]/ cast(sum([Count of customer after basic plan]) over () as decimal(10,2)))*100 as decimal(10,1))<br>as [% of Customers in each plan after free trial plan]<br>from cte2</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/694/1*h0SVWHowYL-codrquFuC-g.png" /></figure><p><strong><em>Insight:-<br>More than 80% of customers are on paid plans but a small 3.7% on plan 3 (pro annual $199). <br>Foodie-Fi has to strategize on their customer acquisition who would be willing to spend more.<br>There is a good point then only 9% is the churn after free trial, we can still try to reduce it.</em></strong></p><p><strong>Q7. What is the customer count and percentage breakdown of all 5 plan_name values at 2020–12–31?</strong></p><p>select * from plans<br>select * from subscriptions</p><p><strong><em>My solution:</em></strong></p><p><strong><em>trying to find customers who are actually active in any plan as of Dec 31, 2020</em></strong></p><p><strong><em>First I am calculating start_date &amp; end_date (calculated acc to the plan validity) of all customers all plans and then to get the customers who are active as on Dec 31, 2020 — start_date should be before it and end date must be after it<br>in this those monthly plans will not be counted which started in Ictber, November, but ended before Dec 31, 2020</em></strong><br><br>;<br>with cte as (<br>select customer_id, start_date, plan_name, <br> — end date will decide the end of the plan based on start_date and the plan<br>case when plan_name = ‘trial’ then DATEADD(day, 7, start_date) <br> when plan_name = ‘basic monthly’ then DATEADD(month, 1, start_date) <br> when plan_name = ‘pro monthly’ then DATEADD(month, 1, start_date) <br> when plan_name = ‘pro annual’ then DATEADD(month, 12, start_date) <br> end as end_date<br>from subscriptions s join plans p on p.plan_id = s.plan_id — where customer_id = 576<br>)<br>, cte1 as (<br> — to get the customers who are active as on Dec 31, 2020 — start_date should be before it and end date must be after it<br>select * from cte where start_date &lt;= ‘2020–12–31’ and end_date &gt; ‘2020–12–31’<br>)<br>, cte2 as (<br>select plan_name, count(customer_id) as [Count of Customers]<br>from cte1 <br> — where start_date &lt;= ‘2020–12–31’ and end_date &gt; ‘2020–12–31’ <br>group by plan_name<br>)<br>select plan_name, [Count of Customers], — sum([Count of Customers]) over () as total,<br>cast(([Count of Customers]/cast(sum([Count of Customers]) over () as decimal(10,2)))*100 as decimal(10,2)) as percentage<br>from cte2</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/334/1*NmUNYW8l0ahyjOHhxCMlnw.png" /></figure><p><strong>— chat gpt solution</strong> (this is also taking into consideration those monthly plans which started in October, <br> — but won’t be active till December, e.g. customer_id = 5<br>;<br>WITH latest_subscriptions AS (<br> SELECT <br> customer_id,<br> plan_id, start_date,<br> ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY start_date DESC) AS rn<br> FROM subscriptions<br> WHERE start_date &lt;= ‘2020–12–31’<br>),<br>latest_plan AS (<br> SELECT ls.customer_id, ls.plan_id, start_date<br> FROM latest_subscriptions ls<br> WHERE ls.rn = 1<br>)<br> — select * from latest_plan<br>SELECT <br> p.plan_name,<br> COUNT(lp.customer_id) AS customer_count,<br> cast(COUNT(lp.customer_id) * 1.0 / cast(SUM(COUNT(lp.customer_id)) OVER () as decimal(10,2)) * 100 as decimal(10,2)) AS percentage<br>FROM latest_plan lp<br>RIGHT JOIN plans p ON lp.plan_id = p.plan_id<br>GROUP BY p.plan_name</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/319/1*sErgXus1W_BSH8VIcKYWAQ.png" /></figure><h4><strong>— tell me what’s the correct solution according to you and why?</strong></h4><p><strong>Q8. How many customers have upgraded to an annual plan in 2020?</strong><br> <strong><em>— here we just have to count number of customers for plan_id = 3 and year of start date should be 2020 only</em></strong></p><p><strong>Solution#8:<em><br></em></strong>select count(*) as upgraded_customers_to_annual_plan <br>from subscriptions <br>where plan_id = 3 — annual pro plan<br>and year(start_date) = 2020</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/267/1*yCqW7Tye0_LP66rSs6Dh8A.png" /></figure><p><strong><em>Insights: — 195 customers upgraded to an annual plan in 2020.</em></strong></p><p><strong>Q9. How many days on average does it take for a customer to an annual plan from the day they join Foodie-Fi?</strong></p><p><strong>Solution #9:</strong></p><p>with cte as (<br>select distinct customer_id, min(start_date) over (partition by customer_id order by start_date) as First_start_date,<br>case when plan_id = 3 then start_date end as Upgraded_date<br>from subscriptions<br>)<br>, cte1 as (<br>select *, DATEDIFF(day, First_start_date, Upgraded_date) as Days_taken_to_convert <br>from cte <br>where DATEDIFF(day, First_start_date, Upgraded_date) is not null<br>)<br> — select * from cte1<br>select avg(days_taken_to_convert) as Avg_days_taken_to_convert from cte1</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/214/1*L3gNvY_IFcYjLTBd-2gWBQ.png" /></figure><p><strong><em>Insight: On average, it takes 105 days for a customer to upgrade to an annual plan from the day they join Foodie-Fi.<br> — that’s a lot of time acc to me, we can put more efforts to convert customers into annual plan soon.</em></strong></p><p><strong>Q10:Can you further breakdown this average value into 30 day periods (i.e. 0–30 days, 31–60 days etc)</strong><br>;<br>with cte as (<br>select distinct customer_id, min(start_date) over (partition by customer_id order by start_date) as First_start_date,<br>case when plan_id = 3 then start_date end as Upgraded_date<br>from subscriptions<br>)<br>, cte1 as (<br>select *, DATEDIFF(day, First_start_date, Upgraded_date) as Days_taken_to_convert <br>from cte <br>where DATEDIFF(day, First_start_date, Upgraded_date) is not null<br>)<br>, cte2 as (<br>select *, <br>case when days_taken_to_convert &gt;=0 and days_taken_to_convert &lt; 30 then ‘0–30 days’<br> when days_taken_to_convert &gt;=30 and days_taken_to_convert &lt; 60 then ‘30–60 days’<br> when days_taken_to_convert &gt;=60 and days_taken_to_convert &lt; 90 then ‘60–90 days’<br> when days_taken_to_convert &gt;=90 and days_taken_to_convert &lt; 120 then ‘90–120 days’<br> when days_taken_to_convert &gt;=120 and days_taken_to_convert &lt; 150 then ‘120–150 days’<br> when days_taken_to_convert &gt;=150 and days_taken_to_convert &lt; 180 then ‘150–180 days’<br> when days_taken_to_convert &gt;=180 and days_taken_to_convert &lt;210 then ‘180–210 days’<br> when days_taken_to_convert &gt;=210 and days_taken_to_convert &lt; 240 then ‘210–240 days’<br> when days_taken_to_convert &gt;=240 and days_taken_to_convert &lt; 270 then ‘240–270 days’<br> — periods may be more<br>end as Period<br>from cte1<br>)<br>select Period, count(*) as [Count of Customers] <br>from cte2 <br>group by Period <br>order by CASE <br> WHEN Period = ‘0–30 days’ THEN 1<br> WHEN Period = ‘30–60 days’ THEN 2<br> WHEN Period = ‘60–90 days’ THEN 3<br> WHEN Period = ‘90–120 days’ THEN 4<br> WHEN Period = ‘120–150 days’ THEN 5<br> WHEN Period = ‘150–180 days’ THEN 6<br> WHEN Period = ‘180–210 days’ THEN 7<br> WHEN Period = ‘210–240 days’ THEN 8<br> — Add more periods as needed<br> END;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/256/1*S0_FGN_b5CsccV60udvYAA.png" /></figure><p><strong>Q11: How many customers downgraded from a pro monthly (plan_id = 2) to a basic monthly plan (plan_id = 1) in 2020?</strong></p><p><strong><em>basically we can check if pro plan date &lt; basic plan date means customer has taken pro plan first and then downgraded to basic plan<br> i.e the difference between pro plan date and basic plan date must be -ve<br> e.g if a customer has taken pro plan on 01–01–2021 and downgraded to basic plan on 10–01–2021 the difference is -10 so this is our result</em></strong></p><p>;<br>with cte as (<br>select customer_id,<br>DATEDIFF(day, min(case when plan_id = 1 then start_date end), min(case when plan_id = 2 then start_date end)) as diff<br>from subscriptions where plan_id in (1,2)<br>and year(start_date) = 2020<br>group by customer_id<br>)<br>select * from cte where diff &lt; 0</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/167/1*eGBBfK5TQLI9vlgYgjfHEA.png" /></figure><p><strong><em>Insight: In the year 2020, no customer downgraded from pro monthly to basic monthly.</em></strong></p><p>—<strong><em> Alternative solution</em></strong><br><br><strong><em>We can checck the next plan_id of customers after plan_id = 2 (pro monthly) by using lead window function.<br>If the next plan_id after plan_id 3 is 2 then we count them as customers who downgraded from pro monthly to basic monthly plan</em></strong><br><br>with cte as (<br>select *, lead(plan_id) over (partition by customer_id order by start_date) as next_plan_id<br>from subscriptions<br>where year(start_date) = 2020<br>)<br>select count(distinct customer_id) as [Count of customers] from cte where plan_id = 2 and next_plan_id = 1</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/171/1*pGbqUUILvKW_6InTjjAqVw.png" /></figure><p><strong><em>Insight: In the year 2020, no customer downgraded from pro monthly to basic monthly.</em></strong></p><h3><strong>This covers solutions of Section A and Section B.</strong></h3><blockquote>Thanks for reading till here.</blockquote><blockquote><strong>For any feedback or improvements please write on akshatagrawal169@gmail.com</strong></blockquote><blockquote>Connect with us on <a href="https://www.youtube.com/channel/UCOuQ1UMKMKGEey4-fxQVSCQ"><strong><em>YouTube</em> </strong></a>to get more such updates.<br>Follow us on <a href="https://www.instagram.com/the_dataholics/"><strong><em>Instagram </em></strong></a>page to get more information on <strong>Data Science &amp; Analytics.</strong></blockquote><blockquote><strong>Get top SQL interview questions for data/business analyst </strong><a href="https://medium.com/@akshatagrawal9431/mastering-advanced-sql-practical-interview-questions-for-2024-94de0d3e0bdb"><strong>here</strong></a><strong>.</strong></blockquote><blockquote><strong>For end to end SQL Portfolio Project (Pizza Case Study) click </strong><a href="https://medium.com/@akshatagrawal9431/sql-pizza-case-study-project-portfolio-5a8afa69e265"><strong><em>here</em></strong></a><strong><em>.</em></strong></blockquote><blockquote><strong>If you want to understand the concept of CTEs vs Temporary tables in SQL, click </strong><a href="https://medium.com/@akshatagrawal9431/temporary-tables-and-common-table-expression-ctes-in-sql-1072353224d4"><strong><em>here </em></strong></a><strong>read my other blog in which I have the difference and use cases of them.</strong></blockquote><blockquote><strong>Understand the order of execution in SQL by clicking </strong><a href="https://medium.com/@akshatagrawal9431/sql-order-of-execution-82b595ef4e8b"><strong><em>here</em></strong></a><strong><em>.</em></strong></blockquote><p><strong><em>To understand SQL Business Analyst Interview Question click </em></strong><a href="https://medium.com/@akshatagrawal9431/sql-business-analyst-interview-question-intermediate-level-29aa59e81692"><strong><em>here</em></strong></a><strong><em>.</em></strong></p><p><strong><em>To undertand SQL Order of Execution click </em></strong><a href="https://medium.com/@akshatagrawal9431/sql-order-of-execution-82b595ef4e8b"><strong><em>here</em></strong></a><strong><em>.</em></strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=322750a905d2" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Mastering Advanced SQL: Practical Interview Questions for 2024]]></title>
            <link>https://medium.com/@akshatagrawal9431/mastering-advanced-sql-practical-interview-questions-for-2024-94de0d3e0bdb?source=rss-a244e194dfaf------2</link>
            <guid isPermaLink="false">https://medium.com/p/94de0d3e0bdb</guid>
            <category><![CDATA[interview-questions]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[akshat-agrawal]]></category>
            <category><![CDATA[interview]]></category>
            <dc:creator><![CDATA[Akshat Agrawal]]></dc:creator>
            <pubDate>Thu, 23 May 2024 07:45:10 GMT</pubDate>
            <atom:updated>2024-05-23T07:45:10.130Z</atom:updated>
            <content:encoded><![CDATA[<p>I’m Akshat Agrawal, a data analyst and entrepreneur passionate about data, content creation, and empowering others with data insights.</p><p>In the world of data, SQL is a must-know skill. As we are in 2024, the importance of SQL in interviews has only grown. Whether you’re an experienced data analyst, a data scientist, or a database administrator, being prepared for tough SQL questions is crucial.</p><p>Picture yourself at your dream job interview, ready to tackle any SQL question with ease. This blog is your go-to guide. I’m sharing Data Analyst interview questions based on which I was asked in recent interviews and some from top SQL practice platforms.</p><p>These questions go beyond basic commands. They test your problem-solving skills, query optimization, and understanding of complex database concepts. By reading this blog, you’ll know what to expect and how to impress with your answers.</p><p><strong>Why read this blog?</strong></p><p>It’s more than just questions. You’ll get detailed explanations and insights to improve your SQL skills. Each question helps you get better, ensuring you’re ready for interviews and real-world challenges.</p><p><strong>Let’s get started!</strong></p><p><strong>Question #1.Y-on-Y Growth Rate [Wayfair SQL Interview Question]</strong></p><p><a href="https://datalemur.com/questions/yoy-growth-rate"><strong>Link</strong></a></p><p><strong>Assume you’re given a table containing information about Wayfair user transactions for different products. Write a query to calculate the </strong><a href="https://www.fundera.com/blog/year-over-year-growth"><strong>year-on-year growth rate</strong></a><strong> for the total spend of each product, grouping the results by product ID.</strong></p><p><strong>The output should include the year in ascending order, product ID, current year’s spend, previous year’s spend and year-on-year growth percentage, rounded to 2 decimal places.</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/320/1*rLHBbbjFNt5hzs5-4_SFGg.png" /></figure><h4>Solution #1:</h4><p>use database_name</p><p>– Let’s first create the table in our system to practice -</p><p>CREATE TABLE user_transactions (</p><p>transaction_id INTEGER PRIMARY KEY,</p><p>product_id INTEGER,</p><p>spend DECIMAL(10, 2),</p><p>transaction_date DATETIME</p><p>);</p><p>— inserting sample records for practice -</p><p>INSERT INTO user_transactions</p><p>(transaction_id, product_id, spend, transaction_date) VALUES</p><p>(1, 101, 50.00, ‘2022–01–15’),</p><p>(2, 101, 75.00, ‘2022–02–20’),</p><p>(3, 102, 100.00, ‘2022–03–10’),</p><p>(4, 102, 150.00, ‘2023–01–12’),</p><p>(5, 103, 200.00, ‘2023–02–25’),</p><p>(6, 103, 250.00, ‘2022–05–30’),</p><p>(7, 104, 300.00, ‘2022–07–14’),</p><p>(8, 104, 350.00, ‘2023–06–18’),</p><p>(9, 105, 400.00, ‘2023–08–20’),</p><p>(10, 105, 450.00, ‘2022–09–25’),</p><p>(11, 106, 500.00, ‘2022–10–30’),</p><p>(12, 106, 550.00, ‘2023–11–10’),</p><p>(13, 107, 600.00, ‘2022–12–15’),</p><p>(14, 107, 650.00, ‘2023–04–20’),</p><p>(15, 108, 700.00, ‘2023–03–25’),</p><p>(16, 108, 750.00, ‘2022–11–30’),</p><p>(17, 109, 800.00, ‘2022–06–05’),</p><p>(18, 109, 850.00, ‘2023–05–10’),</p><p>(19, 110, 900.00, ‘2023–07–15’),</p><p>(20, 110, 950.00, ‘2022–08–20’);</p><p>SELECT * FROM user_transactions;</p><p><strong>Let’s understand the logic used below:</strong></p><p><strong>Step 1:</strong> Create the First Common Table Expression (CTE)</p><p>- Creates a temporary result set named `cte`.</p><p>- Selects each `product_id`, the year of each transaction, and the total spend amount for each product in each year.</p><p>- Groups the results by `product_id` and year.</p><p><strong>Step 2:</strong> Create the Second Common Table Expression (CTE1)</p><p>- Creates another temporary result set named `cte1`.</p><p>- Selects the year, `product_id`, the current year’s spend as `curr_year_spend`, and the previous year’s spend using the `LAG` function.</p><p>- The `LAG` function retrieves the previous row’s spend amount within the same `product_id` group, ordered by year.</p><p><strong>Step 3:</strong> Final Selection and Calculation</p><p>- Selects everything from `cte1`.</p><p>- Calculates the Year-over-Year (YoY) growth rate as:</p><p>- Subtracting the previous year’s spend (`prev_year_spend`) from the current year’s spend (`curr_year_spend`).</p><p>- Dividing the difference by the previous year’s spend.</p><p>- Multiplying by 100 to convert it to a percentage.</p><p>- Rounding the result to 2 decimal places.</p><p>- Labels the result as `yoy_rate`.</p><p><strong>Summary</strong></p><p>1. First CTE: Aggregates total spend for each product by year.</p><p>2. Second CTE: Uses the `LAG` function to get the previous year’s spend for each product.</p><p>3. Final Query: Calculates the YoY growth rate for each product and year, showing how the spend has changed compared to the previous year.</p><p>This structure helps us to see the yearly spend trends for each product and understand their performance over time.</p><p><strong>— SQL SERVER Solution</strong></p><p>with cte as (</p><p>select product_id, year(transaction_date) as year,</p><p>sum(spend) as Spent_Amount</p><p>from user_transactions</p><p>group by product_id, year(transaction_date)</p><p>— order by product_id, year(transaction_date)</p><p>)</p><p>, cte1 as (</p><p>select year, product_id, spent_amount as curr_year_spend,</p><p>lag(spent_amount) over (partition by product_id order by year) as</p><p>prev_year_spend</p><p>from cte</p><p>)</p><p>select *,</p><p>round(((curr_year_spend — prev_year_spend)/prev_year_spend) *100, 2)</p><p>as yoy_rate</p><p>from cte1</p><p><strong>— PostGRE SQL solution</strong></p><p>with cte as (</p><p>select product_id, EXTRACT(YEAR FROM transaction_date) as year,</p><p>sum(spend) as Spent_Amount</p><p>from user_transactions</p><p>group by product_id, EXTRACT(YEAR FROM transaction_date)</p><p>order by product_id, EXTRACT(YEAR FROM transaction_date)</p><p>)</p><p>, cte1 as (</p><p>select year, product_id, spent_amount as curr_year_spend,</p><p>lag(spent_amount) over (partition by product_id order by year) as</p><p>prev_year_spend</p><p>from cte</p><p>)</p><p>select *,</p><p>round(((curr_year_spend — prev_year_spend)/prev_year_spend) *100, 2)</p><p>as yoy_rate</p><p>from cte1</p><p><strong>Question #2: Calculate run rate after each ball in a cricket match. We are given a table in which we have match_id, ball_number, innings_no, over, outcome, batsman, bowler, comment, score.</strong></p><p><strong>Note: If there is a NO ball or wide ball, the ball_number increases but the over remains the same.</strong></p><p>use db_name;</p><p>– Let’s create such a table in our system to practice:</p><p>CREATE TABLE cricket_data ( match_id INT, ball_number INT, innings_no INT, over_ DECIMAL (3,2), outcome VARCHAR(50), batter VARCHAR(100), bowler VARCHAR(100), comment varchar(100), score INT );</p><p>select * from cricket_data;</p><p>— inserting sample records to practice:</p><p>INSERT INTO cricket_data</p><p>(match_id, ball_number, innings_no, over_, outcome, batter, bowler, comment, score)</p><p>VALUES</p><p>(2, 1, 2, 0.1, ‘4’, ‘Batter One’, ‘Bowler A’, ‘Off the mark with a boundary!’, 4),</p><p>(2, 2, 2, 0.2, ‘0’, ‘Batter One’, ‘Bowler A’, ‘Defended back to the bowler.’, 0),</p><p>(2, 3, 2, 0.3, ‘1’, ‘Batter One’, ‘Bowler A’, ‘Tucked away for a single.’, 1),</p><p>(2, 4, 2, 0.4, ‘0’, ‘Batter Two’, ‘Bowler A’, ‘Big swing and a miss.’, 0),</p><p>(2, 5, 2, 0.5, ‘2’, ‘Batter Two’, ‘Bowler A’, ‘Driven past cover for two.’, 2),</p><p>(2, 6, 2, 0.6, ‘W’, ‘Batter Two’, ‘Bowler A’, ‘Knocked him over! The bails are off!’, 0),</p><p>(2, 7, 2, 1.1, ‘0’, ‘Batter Three’, ‘Bowler B’, ‘Straight to the fielder. No run.’, 0),</p><p>(2, 8, 2, 1.2, ‘3’, ‘Batter Three’, ‘Bowler B’, ‘Good running between the wickets for three.’, 3),</p><p>(2, 9, 2, 1.3, ‘1’, ‘Batter One’, ‘Bowler B’, ‘Flicked to the leg side for a quick single.’, 1),</p><p>(2, 10, 2, 1.4, ‘1’, ‘Batter Three’, ‘Bowler B’, ‘One more to the score.’, 1),</p><p>(2, 11, 2, 1.5, ‘0’, ‘Batter One’, ‘Bowler B’, ‘Straight to the fielder again.’, 0),</p><p>(2, 12, 2, 1.6, ‘6’, ‘Batter One’, ‘Bowler B’, ‘What a shot! Six runs!’, 6),</p><p>(2, 13, 2, 2.1, ‘4’, ‘Batter Three’, ‘Bowler A’, ‘Cracking shot through mid-wicket for four.’, 4),</p><p>(2, 14, 2, 2.2, ‘1’, ‘Batter Three’, ‘Bowler A’, ‘Single taken.’, 1),</p><p>(2, 15, 2, 2.3, ‘1’, ‘Batter One’, ‘Bowler A’, ‘Another quick single.’, 1),</p><p>(2, 16, 2, 2.4, ‘0’, ‘Batter Three’, ‘Bowler A’, ‘Defended with no run.’, 0),</p><p>(2, 17, 2, 2.5, ‘1nb’, ‘Batter Three’, ‘Bowler A’, ‘No ball called for overstepping.’, 1),</p><p>(2, 18, 2, 2.5, ‘1’, ‘Batter Three’, ‘Bowler A’, ‘Free hit ball goes for a single.’, 1),</p><p>(2, 19, 2, 2.6, ‘1lb’, ‘Batter One’, ‘Bowler A’, ‘Leg bye taken.’, 1),</p><p>(2, 20, 2, 3.1, ‘2’, ‘Batter Three’, ‘Bowler B’, ‘Quick between the wickets, two runs scored.’, 2),</p><p>(2, 21, 2, 3.2, ‘0’, ‘Batter Three’, ‘Bowler B’, ‘No connection there, missed the ball.’, 0),</p><p>(2, 22, 2, 3.3, ‘W’, ‘Batter Three’, ‘Bowler B’, ‘Caught at slip! Another one bites the dust.’, 0),</p><p>(2, 23, 2, 3.4, ‘1’, ‘Batter Four’, ‘Bowler B’, ‘Off the mark with a single.’, 1),</p><p>(2, 24, 2, 3.5, ‘4’, ‘Batter Four’, ‘Bowler B’, ‘Beautiful drive, four runs!’, 4),</p><p>(2, 25, 2, 3.6, ‘0’, ‘Batter Four’, ‘Bowler B’, ‘Attempted cut, misses.’, 0),</p><p>(2, 26, 2, 4.1, ‘0’, ‘Batter One’, ‘Bowler A’, ‘Solid defense.’, 0),</p><p>(2, 27, 2, 4.2, ‘2’, ‘Batter One’, ‘Bowler A’, ‘They pick up a couple.’, 2),</p><p>(2, 28, 2, 4.3, ‘1’, ‘Batter One’, ‘Bowler A’, ‘Quick single to mid-on.’, 1),</p><p>(2, 29, 2, 4.4, ‘0’, ‘Batter Four’, ‘Bowler A’, ‘Plays and misses.’, 0),</p><p>(2, 30, 2, 4.5, ‘4’, ‘Batter Four’, ‘Bowler A’, ‘Cut away for four!’, 4),</p><p>(2, 31, 2, 4.6, ‘1’, ‘Batter Four’, ‘Bowler A’, ‘Theyll take one more.’, 1),</p><p>(2, 32, 2, 5.1, ‘1’, ‘Batter One’, ‘Bowler B’, ‘Single to the leg side.’, 1),</p><p>(2, 33, 2, 5.2, ‘0’, ‘Batter Four’, ‘Bowler B’, ‘A sharp bouncer! The batsman ducks.’, 0),</p><p>(2, 34, 2, 5.3, ‘0’, ‘Batter Four’, ‘Bowler B’, ‘Solid block.’, 0),</p><p>(2, 35, 2, 5.4, ‘0’, ‘Batter Four’, ‘Bowler B’, ‘Tried to flick, but missed. Hits the pads.’, 0),</p><p>(2, 36, 2, 5.5, ‘2’, ‘Batter Four’, ‘Bowler B’, ‘Driven through covers, they are back for two.’, 2),</p><p>(2, 37, 2, 5.6, ‘1’, ‘Batter Four’, ‘Bowler B’, ‘Ends the over with a drive to long-off for a single.’, 1),</p><p>(2, 38, 2, 6.1, ‘1’, ‘Batter Five’, ‘Bowler C’, ‘Singles to start the over.’, 1),</p><p>(2, 39, 2, 6.2, ‘1’, ‘Batter One’, ‘Bowler C’, ‘Pushed to the off side for a quick run.’, 1),</p><p>(2, 40, 2, 6.3, ‘1nb’, ‘Batter One’, ‘Bowler C’, ‘Oversteps for a no-ball. Single taken.’, 1),</p><p>(2, 41, 2, 6.3, ‘4’, ‘Batter One’, ‘Bowler C’, ‘Hits the free hit delivery for four!’, 4),</p><p>(2, 42, 2, 6.4, ‘1’, ‘Batter Five’, ‘Bowler C’, ‘Drives to mid-off for a single.’, 1),</p><p>(2, 43, 2, 6.5, ‘1’, ‘Batter One’, ‘Bowler C’, ‘Goes back and cuts for one run.’, 1),</p><p>(2, 44, 2, 6.6, ‘1w’, ‘Batter Five’, ‘Bowler C’, ‘Wide ball outside off stump.’, 1),</p><p>(2, 45, 2, 6.6, ‘1’, ‘Batter Five’, ‘Bowler C’, ‘Nudged behind square for a single.’, 1),</p><p>(2, 46, 2, 7.1, ‘1’, ‘Batter One’, ‘Bowler C’, ‘A gentle push into the covers for one.’, 1),</p><p>(2, 47, 2, 7.2, ‘2’, ‘Batter Five’, ‘Bowler D’, ‘Clipped off the pads, theyre back for two.’, 2),</p><p>(2, 48, 2, 7.3, ‘1’, ‘Batter Five’, ‘Bowler D’, ‘Steered to third man for a single.’, 1),</p><p>(2, 49, 2, 7.4, ‘1’, ‘Batter One’, ‘Bowler D’, ‘Quick single to midwicket.’, 1),</p><p>(2, 50, 2, 7.5, ‘1’, ‘Batter Five’, ‘Bowler D’, ‘Flicks it off his pads for one run.’, 1),</p><p>(2, 51, 2, 7.6, ‘1’, ‘Batter One’, ‘Bowler D’, ‘A fast single taken to keep the scoreboard ticking.’, 1)</p><p>, (2, 52, 2, 8.1, ‘1’, ‘Batter Five’, ‘Bowler E’, ‘Off the edge, but safe. They get one.’, 1),</p><p>(2, 53, 2, 8.2, ‘0’, ‘Batter One’, ‘Bowler E’, ‘Good length delivery, defended.’, 0),</p><p>(2, 54, 2, 8.3, ‘4’, ‘Batter One’, ‘Bowler E’, ‘Edged and through the slips for four!’, 4),</p><p>(2, 55, 2, 8.4, ‘0’, ‘Batter One’, ‘Bowler E’, ‘Tried to cut, but straight to the point fielder.’, 0),</p><p>(2, 56, 2, 8.5, ‘1w’, ‘Batter One’, ‘Bowler E’, ‘Wide ball down the leg side.’, 1),</p><p>(2, 57, 2, 8.5, ‘0’, ‘Batter One’, ‘Bowler E’, ‘Defensive shot to cover. No run.’, 0),</p><p>(2, 58, 2, 8.6, ‘2’, ‘Batter One’, ‘Bowler E’, ‘Pulled away for a couple of runs.’, 2),</p><p>(2, 59, 2, 9.1, ‘1’, ‘Batter Five’, ‘Bowler D’, ‘Singles seem to be the strategy here.’, 1),</p><p>(2, 60, 2, 9.2, ‘6’, ‘Batter One’, ‘Bowler D’, ‘He’s hit that high and mighty — Six!’, 6),</p><p>(2, 61, 2, 9.3, ‘0’, ‘Batter One’, ‘Bowler D’, ‘Swing and a miss. The bowler beats the bat.’, 0),</p><p>(2, 62, 2, 9.4, ‘0’, ‘Batter One’, ‘Bowler D’, ‘Full delivery, driven straight to mid-off.’, 0),</p><p>(2, 63, 2, 9.5, ‘1’, ‘Batter One’, ‘Bowler D’, ‘Theyll sneak a quick single.’, 1),</p><p>(2, 64, 2, 9.6, ‘1’, ‘Batter Five’, ‘Bowler D’, ‘A delicate touch to the on side for one.’, 1);</p><p>– see the table here</p><p>select * from cricket_data</p><p>/*</p><p><strong>— Solution Explanation:</strong></p><p>In our data when there is any NO or WIDE ball the ball_number is increased but the over_ remains the same. We are creating a new ball column to encounter NO or WIDE balls.</p><p>Example:</p><p>if over_ is 4.3 that means 4 overs and 3 balls -&gt; 27 balls.</p><p>Formula for ball_number = 4*6 + (4.3–4)*10 =&gt; 27 ball</p><p>i.e floor(over) * 6 + (over — floor(over) *10)</p><p><strong>Final Solution Approach</strong></p><p>Step 1: Calculate the ball number column to get exact ball number</p><p>(using above formula)</p><p>Step 2: Calculate cumulative sum to get score after each ball.</p><p>Step 3: Calculate run rate on each ball by below formula</p><p><strong>(total_runs_after_that_ball*6)/ball</strong></p><p>*/</p><p>;</p><p>with cte as (</p><p>select ball_number, over_,</p><p>cast(((FLOOR(over_)*6) + ((over_ — floor(over_))*10)) as int) as ball,</p><p>score</p><p>— sum(score) over (order by ball_number) as c_sum</p><p>from cricket_data</p><p>)</p><p>— now let’s calculate cumulative score after each ball</p><p>, cte1 as (</p><p>select over_, ball, score,</p><p>sum(score) over (order by ball) as c_sum</p><p>from cte</p><p>)</p><p>select ball, c_sum, cast((c_sum*6.00/ball) as decimal(10,2)) as rr</p><p>from cte1;</p><p><strong>Question #3: You have a table UserPurchases with the following columns:</strong></p><p>UserID (integer), PurchaseID (integer), PurchaseDate (date),</p><p>Amount (decimal)</p><p>This table records the purchases made by users. Each user can have multiple purchase records.</p><p><strong>Task:</strong></p><p>Write a SQL query to find the first purchase that each user made immediately after a purchase of at least 100rs.</p><p>Provide the UserID, PurchaseID, and PurchaseDate for these subsequent purchases.</p><p>/*</p><p><strong>Explanation of the logic used -</strong></p><p>We have to see the immediate next purchase after the purchase of at least 100 rs so we have used case statement (when amount &gt;= 100) and lead(amount) to get the immediate next record. It should be for each user so we used partition by userid and to get the next purchase of each user, and then sorted the data in ascending order of date (order by PurchaseDate)</p><p>*/</p><p><strong>Solution #3</strong></p><p>use db_name;</p><p>CREATE TABLE UserPurchases (</p><p>UserID INT,</p><p>PurchaseID INT,</p><p>PurchaseDate DATE,</p><p>Amount DECIMAL(10, 2)</p><p>);</p><p>— Inserting sample records into the UserPurchases table</p><p>INSERT INTO UserPurchases</p><p>(UserID, PurchaseID, PurchaseDate, Amount) VALUES</p><p>(1, 101, ‘2024–01–01’, 50.00),</p><p>(1, 102, ‘2024–01–05’, 120.00),</p><p>(1, 103, ‘2024–01–10’, 80.00),</p><p>(2, 201, ‘2024–02–02’, 150.00),</p><p>(2, 202, ‘2024–02–10’, 90.00),</p><p>(3, 301, ‘2024–03–15’, 200.00),</p><p>(3, 302, ‘2024–03–20’, 70.00),</p><p>(3, 303, ‘2024–03–25’, 130.00);</p><p>select * from UserPurchases</p><p>;</p><p>with cte as (</p><p>select *, case when amount&gt;=100 then lead(amount) over (partition by userid order by purchasedate) end as ‘next_purchase’</p><p>from UserPurchases</p><p>)</p><p>select * from cte where next_purchase is not NULL</p><p>Thanks for reading till here. If you get some value, follow me for more such content.</p><p>For any feedback or improvements please write on akshatagrawal169@gmail.com</p><p>Connect with me on <a href="https://www.youtube.com/channel/UCOuQ1UMKMKGEey4-fxQVSCQ"><strong><em>YouTube</em> </strong></a>to get more such updates.<br>Follow me on <a href="https://www.instagram.com/the_dataholics/"><strong><em>Instagram </em></strong></a>to get more information on <strong>Data Science &amp; Analytics.</strong></p><p><strong>For end to end SQL Portfolio Project (Pizza Case Study) click </strong><a href="https://medium.com/@akshatagrawal9431/sql-pizza-case-study-project-portfolio-5a8afa69e265"><strong><em>here</em></strong></a><strong><em>.</em></strong></p><p><strong>If you want to understand the concept of CTEs vs Temporary tables in SQL, click </strong><a href="https://medium.com/@akshatagrawal9431/temporary-tables-and-common-table-expression-ctes-in-sql-1072353224d4"><strong><em>here </em></strong></a><strong>read my other blog in which I have the difference and use cases of them.</strong></p><p><strong>Understand the Order of Execution in SQL by clicking </strong><a href="https://medium.com/@akshatagrawal9431/sql-order-of-execution-82b595ef4e8b"><strong><em>here</em></strong></a><strong><em>.</em></strong></p><p><strong>To practice SQL with a business case study, click </strong><a href="https://medium.com/@akshatagrawal9431/week-4-data-bank-sql-solutions-ac8f25905fc2"><strong><em>here </em></strong></a>to <strong>read my other blog in which I have shared reference to some questions and their solutions.</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=94de0d3e0bdb" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQL Business Analyst Interview Question | Intermediate Level]]></title>
            <link>https://medium.com/@akshatagrawal9431/sql-business-analyst-interview-question-intermediate-level-29aa59e81692?source=rss-a244e194dfaf------2</link>
            <guid isPermaLink="false">https://medium.com/p/29aa59e81692</guid>
            <category><![CDATA[interview-questions]]></category>
            <category><![CDATA[sql-interview-questions]]></category>
            <category><![CDATA[interview]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Akshat Agrawal]]></dc:creator>
            <pubDate>Wed, 15 May 2024 12:33:57 GMT</pubDate>
            <atom:updated>2024-05-15T12:41:22.574Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*VW5b17fbQGeYNjvvoS_SvA.png" /></figure><p>Click <a href="https://www.youtube.com/watch?v=gRQuYPKSy9A&amp;ab_channel=the_dataholics"><strong><em>here </em></strong></a>to watch the video</p><p><strong>Question #1: We have a table called player_appearances in which we have match_id, batsmen, bowler name. Write a SQL query to find the list of players with their match_id.</strong></p><p><strong>Solution #1:</strong></p><p>use database_name;</p><p>/* Let’s create a table in our system -</p><p>CREATE TABLE player_appearances (</p><p>match_id INT,</p><p>batsman VARCHAR(100),</p><p>bowler VARCHAR(100)</p><p>);</p><p>INSERT INTO player_appearances (match_id, batsman, bowler) VALUES</p><p>(1, &#39;Virat Kohli&#39;, &#39;Jasprit Bumrah&#39;),</p><p>(1, &#39;Rohit Sharma&#39;, &#39;Jasprit Bumrah&#39;),</p><p>(2, &#39;Virat Kohli&#39;, &#39;Bhuvneshwar Kumar&#39;),</p><p>(2, &#39;Shikhar Dhawan&#39;, &#39;Bhuvneshwar Kumar&#39;),</p><p>(3, &#39;Virat Kohli&#39;, &#39;Yuzvendra Chahal&#39;),</p><p>(3, &#39;KL Rahul&#39;, &#39;Yuzvendra Chahal&#39;),</p><p>(4, &#39;Rohit Sharma&#39;, &#39;Mohammed Shami&#39;),</p><p>(4, &#39;Shreyas Iyer&#39;, &#39;Mohammed Shami&#39;),</p><p>(5, &#39;Virat Kohli&#39;, &#39;Ravindra Jadeja&#39;),</p><p>(5, &#39;Rohit Sharma&#39;, &#39;Ravindra Jadeja&#39;),</p><p>(6, &#39;Shikhar Dhawan&#39;, &#39;Jasprit Bumrah&#39;),</p><p>(6, &#39;KL Rahul&#39;, &#39;Jasprit Bumrah&#39;),</p><p>(7, &#39;Virat Kohli&#39;, &#39;Bhuvneshwar Kumar&#39;),</p><p>(7, &#39;Shreyas Iyer&#39;, &#39;Bhuvneshwar Kumar&#39;),</p><p>(8, &#39;Rohit Sharma&#39;, &#39;Yuzvendra Chahal&#39;),</p><p>(8, &#39;Shikhar Dhawan&#39;, &#39;Yuzvendra Chahal&#39;),</p><p>(9, &#39;Virat Kohli&#39;, &#39;Mohammed Shami&#39;),</p><p>(9, &#39;KL Rahul&#39;, &#39;Mohammed Shami&#39;),</p><p>(10, &#39;Shreyas Iyer&#39;, &#39;Ravindra Jadeja&#39;),</p><p>(10, &#39;Rohit Sharma&#39;, &#39;Ravindra Jadeja&#39;),</p><p>(2, &#39;Yuzvendra Chahal&#39;, &#39;Bhuvneshwar Kumar&#39;),</p><p>(4, &#39;Shreyas Iyer&#39;, &#39;Bhuvneshwar Kumar&#39;),</p><p>(7, &#39;Rohit Sharma&#39;,&#39;Virat Kohli&#39; ),</p><p>(8, &#39;Mohammed Shami&#39;, &#39;Yuzvendra Chahal&#39;),</p><p>(10, &#39;Mohammed Shami&#39;, &#39;Virat Kohli&#39;),</p><p>(3, &#39;KL Rahul&#39;, &#39;Shikhar Dhawan&#39;),</p><p>(2, &#39;Shreyas Iyer&#39;, &#39;Rohit Sharma&#39; ),</p><p>(1, &#39;Ravindra Jadeja&#39;, &#39;Ravindra Jadeja&#39;);</p><p>*/</p><p>select * from player_appearances;</p><ul><li><strong>- Find the list of players with their match_id</strong></li><li><strong>— Approach : Get a list of batsmen with their match_id and bowlers with their match_id and the combine them using UNION operator in SQL</strong></li></ul><p>(select distinct batsman as player,</p><p>match_id from player_appearances )</p><p>union</p><p>(select distinct bowler as player,</p><p>match_id from player_appearances)</p><p>order by batsman</p><p><strong>-- To count the matches played by each player</strong></p><p>;</p><p>with cte as(</p><p>(select distinct batsman as player, match_id from player_appearances)</p><p>union all</p><p>(select distinct bowler as player, match_id from player_appearances)</p><p>)</p><p>-- select distinct player, match_id from cte order by player</p><p>select player, count(distinct match_id) as matches</p><p>from cte group by player</p><p><strong>Question #2: We are given a table of 120 balls -Columns are balls and runs.</strong></p><p><strong>It is telling about runs scored in each ball and we need to find the total runs per over.</strong></p><p><strong>Let’s jump into the database and start creating such a table and the solve the question.</strong></p><p>use database_name;</p><p>drop table if exists match_score;</p><p>— First let’s create such a table in our Database (queries for Ms SQL Server) might differ for different databases</p><p>create table match_score</p><p>( balls int,</p><p>runs int );</p><p>DECLARE @ball INT = 1;</p><p>WHILE @ball &lt;= 120</p><p>BEGIN</p><p>INSERT INTO match_score (balls, runs)</p><p>VALUES (@ball, ROUND(RAND() * (6–1)+1, 0)); — Generates random runs between 0 and 6</p><p>SET @ball = @ball + 1;</p><p>END;</p><p>— now see the table created</p><p>select * from match_score</p><p><strong>Solution Approach:</strong></p><p><strong>— First we create a column for overs — basically creating 20 groups i.e 20 overs using n-tile window function in SQL</strong></p><p>select *, NTILE(20) over(order by balls) as overs</p><p>from match_score;</p><p><strong>— Final Solution (using CTE)</strong></p><p>with cte as (</p><p>select *, NTILE(20) over(order by balls) as overs</p><p>from match_score )</p><p>select overs, sum(runs) as total_runs_per_over</p><p>from cte group by overs</p><p>— runs scored in each over (over consists of 6 balls)</p><p><strong>— using Temporary Table</strong></p><p>select *, NTILE(20) over (order by balls) as overs</p><p>into #t1</p><p>from match_score</p><p>select overs, sum(runs) as sum_runs from #t1 group by overs</p><p>select * from #t1</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=29aa59e81692" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQL Pizza Case Study — Project Portfolio]]></title>
            <link>https://medium.com/@akshatagrawal9431/sql-pizza-case-study-project-portfolio-5a8afa69e265?source=rss-a244e194dfaf------2</link>
            <guid isPermaLink="false">https://medium.com/p/5a8afa69e265</guid>
            <category><![CDATA[sql-project]]></category>
            <category><![CDATA[sql-case-study]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[portfolio]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Akshat Agrawal]]></dc:creator>
            <pubDate>Sun, 05 May 2024 11:21:55 GMT</pubDate>
            <atom:updated>2024-06-16T06:34:37.691Z</atom:updated>
            <content:encoded><![CDATA[<h3>SQL Pizza Case Study — Project Portfolio</h3><p>SQL Project for Portfolio</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tT_mN7QdWOZFTe_26tBR5g.jpeg" /></figure><p>Get the <strong>project context, datasets and SQL queries </strong>used in the project from below link:</p><p><a href="https://github.com/akshatagrawal1998/SQL-Project-Pizza-Case-Study/tree/main/SQL%20Project%20Case%20Study%20(Pizza%20Sales%20Analysis)">Github Link</a></p><p><a href="https://github.com/akshatagrawal1998/SQL-Project-Pizza-Case-Study/tree/main">GitHub - akshatagrawal1998/SQL-Project-Pizza-Case-Study: In this project we are creating a SQL case study. By doing this we&#39;ll learn concepts in SQL from basic to advanced including joins, window functions, subqueries and more.</a></p><h3><strong>Watch the YouTube video to understand the explanations by clicking below:</strong></h3><p><a href="https://www.youtube.com/watch?v=xaRlc4BLZHs&amp;t=3s"><strong><em>YouTube video link</em></strong></a></p><p><strong>Project Overview:</strong></p><p>This SQL project revolves around a database schema designed to manage and analyze data for a pizza store. The database consists of four primary tables:</p><p><em>order_details, pizzas, orders and pizza_types.</em></p><p>Each table plays a crucial role in storing different facets of the business operations, from individual orders to the types of pizzas offered. Below is a detailed description of each table and its columns:</p><p>1. <strong>order_details</strong>:<br> — order_details_id: A unique identifier for each entry in the order details.<br> — order_id: References the ID from the orders table, linking the order detail to a specific order.<br> — pizza_id: References the ID from the pizzas table, identifying which pizza was ordered.<br> — quantity: The number of pizzas ordered of the specified type.</p><p>2. <strong>pizzas</strong>:<br> — pizza_id: A unique identifier for each type of pizza available.<br> — pizza_type_id: Links to the pizza_types table, specifying the type of pizza.<br> — size: The size of the pizza (e.g., small, medium, large).<br> — price: The cost of the pizza.</p><p>3. <strong>orders</strong>:<br> — order_id: A unique identifier for each order placed.<br> — date: The date on which the order was placed.<br> — time: The time at which the order was placed.</p><p>4. <strong>pizza_types</strong>:<br> — pizza_type_id: A unique identifier for each type of pizza.<br> — name: The name of the pizza type (e.g., Margherita, Pepperoni).<br> — category: Categorizes the pizza (e.g., Vegetarian, Non-Vegetarian).<br> — ingredients: Lists the ingredients used in the pizza.</p><p><strong><em>Why does the manager need this analysis?</em></strong></p><p>A pizza sales store manager can utilize this SQL project to extract valuable insights and conduct detailed data analysis, facilitating informed decision-making and efficient management of the store’s operations. Here are a few points illustrating the importance and utility of this database for a store manager:</p><p>- <strong>Sales Analysis</strong>: By querying the order_details and pizzas tables, managers can identify the best-selling pizzas, assess revenue from different pizza sizes, and evaluate pricing strategies.<br>- <strong>Inventory Management</strong>: Analyzing the pizza_types, category, size which are ordered the most helps in managing inventory more efficiently, ensuring that ingredients are stocked according to demand and reducing waste.<br>- <strong>Customer Preferences</strong>: Through data gathered in the orders and pizzas tables, managers can track customer preferences over time, adjusting the menu to cater to popular choices and experimenting with new or seasonal offerings.<br>- <strong>Operational Efficiency</strong>: Date and time data from the orders table allow managers to assess <strong><em>peak hours</em></strong> and staff the store appropriately, ensuring operational efficiency and customer satisfaction.<br>- <strong>Marketing Insights</strong>: Data analysis can also support targeted marketing campaigns, like promotions on specific types of pizzas that are popular or on days when sales are typically lower.</p><p><strong>Conclusion:</strong></p><p>This SQL project not only serves as a robust data management system but also as a strategic tool for business intelligence. By maintaining comprehensive data on every aspect of the store’s operations, the database allows store managers to make precise adjustments to improve both customer experience and profitability. This project can provide practical insights into how structured SQL queries can be used to harness data for real business applications, making it an excellent resource for aspiring data analysts and business owners alike.</p><h3>Watch the YouTube video to understand the explanations by clicking below:</h3><p><a href="https://www.youtube.com/watch?v=xaRlc4BLZHs&amp;t=3s"><strong><em>YouTube video link</em></strong></a></p><p>Thanks for reading till here.</p><p><strong>If you want to practice SQL, click </strong><a href="https://medium.com/@akshatagrawal9431/week-4-data-bank-sql-solutions-ac8f25905fc2"><strong>here </strong></a><strong>to get the solutions of Week #4 from 8 week SQL challenge</strong></p><p><strong>Understand the order of execution in SQL by clicking </strong><a href="https://medium.com/@akshatagrawal9431/sql-order-of-execution-82b595ef4e8b"><strong>here</strong></a><strong>.</strong></p><blockquote><strong>Get top SQL interview questions for data/business analyst </strong><a href="https://medium.com/@akshatagrawal9431/mastering-advanced-sql-practical-interview-questions-for-2024-94de0d3e0bdb"><strong>here</strong></a><strong>.</strong></blockquote><blockquote><strong>For end to end SQL Portfolio Project (Pizza Case Study) click </strong><a href="https://medium.com/@akshatagrawal9431/sql-pizza-case-study-project-portfolio-5a8afa69e265"><strong>here</strong></a><strong>.</strong></blockquote><blockquote><strong>If you want to understand the concept of CTEs vs Temporary tables in SQL, click </strong><a href="https://medium.com/@akshatagrawal9431/temporary-tables-and-common-table-expression-ctes-in-sql-1072353224d4"><strong>here </strong></a><strong>read my other blog in which I have the difference and use cases of them.</strong></blockquote><blockquote><strong>Understand the order of execution in SQL by clicking </strong><a href="https://medium.com/@akshatagrawal9431/sql-order-of-execution-82b595ef4e8b"><strong>here</strong></a><strong>.</strong></blockquote><p><strong><em>To understand SQL Business Analyst Interview Question click </em></strong><a href="https://medium.com/@akshatagrawal9431/sql-business-analyst-interview-question-intermediate-level-29aa59e81692"><strong><em>here</em></strong></a><strong><em>.</em></strong></p><p><strong><em>To undertand SQL Order of Execution click </em></strong><a href="https://medium.com/@akshatagrawal9431/sql-order-of-execution-82b595ef4e8b"><strong><em>here</em></strong></a><strong><em>.</em></strong></p><p>Connect with us on <a href="https://www.youtube.com/channel/UCOuQ1UMKMKGEey4-fxQVSCQ"><strong><em>YouTube</em> </strong></a>to get more such updates.<br>Follow our <a href="https://www.instagram.com/the_dataholics/"><strong><em>Instagram </em></strong></a>page to get more updates on Data Analytics.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5a8afa69e265" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQL Order of Execution]]></title>
            <link>https://medium.com/@akshatagrawal9431/sql-order-of-execution-82b595ef4e8b?source=rss-a244e194dfaf------2</link>
            <guid isPermaLink="false">https://medium.com/p/82b595ef4e8b</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[sql-tutorial]]></category>
            <dc:creator><![CDATA[Akshat Agrawal]]></dc:creator>
            <pubDate>Sun, 17 Mar 2024 14:03:46 GMT</pubDate>
            <atom:updated>2024-05-23T08:53:01.257Z</atom:updated>
            <content:encoded><![CDATA[<p>Hi everyone!<br>This blog covers a very important topic in SQL which is very much needed as we move forward in learning SQL.</p><p><strong>Order of Execution in SQL helps us to:<br></strong>1. Write complex queries in an easy manner (as we have all the understanding)<br>2. Manipulate the query as per requirements</p><p>3. Writing SQL queries easily in SQL cross platforms like MySQL, PostGRE SQL, SQL Server and others.</p><p>Below is the script and notes I used in my the video. You can also watch the video by clicking <a href="https://youtu.be/-2ujeFLEi6E">here</a>.</p><p>— find the students from class 1 and class 2 whose total marks is greater than or equal to 200</p><p>select student, sum(marks) as ‘Total_Marks’<br>from studentmarks <br>where class in (‘Class1’ , ‘Class2’)<br>group by student <br>having sum(marks) &gt;= 200<br>order by student desc</p><p>— Order of execution in SQL:<br>/*<br>from <br>join<br>where <br>group by <br>having <br>select <br>order by<br>top/limit</p><p>— Logical Explanation:<br>so first we’ll see from which table we need data (from) <br>if there are joins (any type) in the query then it is executer with from clause itself to decide which tables do we need data from<br>then we’ll see which data we need (where) — filter<br>then we’ll see whether there is any aggregation (group by) <br>and also it might need us to filter data after aggregation (having)<br>after each of the above operations, display the data (select)<br>and then sort the data (if needed) — order by<br>*/</p><p>Connect with us on <a href="https://www.youtube.com/channel/UCOuQ1UMKMKGEey4-fxQVSCQ"><strong><em>YouTube</em> </strong></a>to get more such updates<br>Follow us on <a href="https://www.instagram.com/the_dataholics/"><strong><em>Instagram </em></strong></a>page to get more information on <strong>Data Science &amp; Analytics.</strong></p><p><strong>Get top SQL interview questions for data/business analyst </strong><a href="https://medium.com/@akshatagrawal9431/mastering-advanced-sql-practical-interview-questions-for-2024-94de0d3e0bdb"><strong>here</strong></a><strong>.</strong></p><p><strong>For end to end SQL Portfolio Project click </strong><a href="https://medium.com/@akshatagrawal9431/sql-pizza-case-study-project-portfolio-5a8afa69e265"><strong><em>here</em></strong></a><strong><em>.</em></strong></p><p><strong>To practice SQL, click </strong><a href="https://medium.com/@akshatagrawal9431/week-4-data-bank-sql-solutions-ac8f25905fc2"><strong><em>here </em></strong></a>to <strong>read my other blog in which I have shared solutions of Week 4 SQL challenge from 8 week SQL Challenge.</strong></p><p><strong>If you want to understand the concept of CTEs vs Temporary tables in SQL, click </strong><a href="https://medium.com/@akshatagrawal9431/temporary-tables-and-common-table-expression-ctes-in-sql-1072353224d4"><strong><em>here </em></strong></a><strong>read my other blog in which I have the difference and use cases of them.</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=82b595ef4e8b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Temporary Tables and Common Table Expression (CTEs) in SQL]]></title>
            <link>https://medium.com/@akshatagrawal9431/temporary-tables-and-common-table-expression-ctes-in-sql-1072353224d4?source=rss-a244e194dfaf------2</link>
            <guid isPermaLink="false">https://medium.com/p/1072353224d4</guid>
            <dc:creator><![CDATA[Akshat Agrawal]]></dc:creator>
            <pubDate>Sun, 17 Dec 2023 06:35:20 GMT</pubDate>
            <atom:updated>2024-05-23T08:53:34.820Z</atom:updated>
            <content:encoded><![CDATA[<p>Let’s try to decode the concept of Temporary Tables in SQL…</p><p>A <strong>CTE (Common Table Expression)</strong> is like a temporary table. We can create a CTE using <strong>WITH </strong>statement, and it can refer to itself in a loop. It’s also called <strong>subquery factoring</strong> and helps make complicated queries simpler and easier to understand.</p><p>CTEs are <strong>not saved on the computer’s storage</strong>, and they <strong>only last for one query execution</strong>. This means we can’t create, change, or delete CTEs directly. Also, we can’t use a CTE in more than one query in the same group of commands.</p><p><strong>Temporary tables</strong> are like temporary data storage areas stored in the <strong>Temporary Tables</strong> (<strong>tempdb) </strong>in<strong> </strong>system database. Unlike CTEs, t<strong>emporary tables are physically saved on the computer’s disk</strong>, and we can make, change, or delete them directly. Temporary tables are useful for holding and working with large amounts of data, and we can use them in more than one query in the same group of commands.</p><p><strong>Global and Local Temporary Tables</strong></p><p>To create temporary tables, we use the CREATE TABLE statement with the prefix <strong>“#” or “##” for local and global temporary tables</strong> respectively. Local temporary tables are only visible to the current session and are automatically removed when the session ends. Global temporary tables are visible to all sessions, and they are automatically removed when the last session that uses them is closed.</p><p>Let’s understand the same with below example:</p><p>Below is an example, when to use CTE and Temporary Tables with use case.</p><p>— use database_name;</p><p><strong>DDL Commands: To create sample data to use</strong></p><p>create table employee (id int, salary int);</p><p>insert into employee values (1,10000), (2,14000),(3,16000),(4,20000),(5,23000),(6,3000),(7,19000),(8,21000),(9,31000);</p><p>select * from employee — to view the table created above</p><p>— <strong>Q. Find the employee with salary greater than the average salary of all employees.</strong></p><p>— <strong>Solution #1: Manual approach</strong>: We calculate average salary and use below solution.</p><p>select * from employee where salary &gt;= 17444 — 17444 (average salary)</p><p>— But the above solution is very specific and manual. If the data change, we have to update the average salary again. Our goal is to create a generalized query which should calculate the average salary itself. So we use subquery to accomplish the goal.</p><p>— Query to calculate the average salary</p><p>select avg(salary) from employee;</p><p>— Let’s solve the same question using CTE</p><p>— <strong>Solution #2:</strong> <strong>Using CTE </strong>— Subquery Factoring— Common Table Expression</p><p><strong>;</strong></p><p><strong>with avg_sal as (</strong></p><p><strong>select avg(salary) as avg_salary from employee</strong></p><p><strong>)</strong></p><p><strong>select e.id, e.salary from employee e, avg_sal where salary &gt;= avg_sal.avg_salary</strong></p><p>— <strong>Explanation</strong>: We are creating a temporary table using with clause which calculates and stores the average salary of all employees in the table and then using it in the below query to compare.</p><p>— Run the above query all at once as the scope of CTEs are while execution only.</p><p><strong>— Solution #3: </strong>Let’s solve the same question using Temp tables</p><p>— creating a temporary table called #avg_sal — (‘#’ is used to create temporary table)</p><p>select avg(salary) as avg_salary into #avg_sal from employee</p><p>select * from #avg_sal;</p><p>drop table #avg_sal; — to drop the temporary table</p><p>select e.id, e.salary from employee e, #avg_sal where salary &gt;= #avg_sal.avg_salary</p><p><strong>— Solution #4: Using subquery</strong></p><p>select * from employee where salary &gt;= (select avg(salary) from employee)</p><h3>Distinguishing Between CTEs and Temporary Tables:</h3><p>1. <strong>Storage</strong>:<br>CTEs: Not physically stored on disk.<br>Temporary Tables: Physically stored on disk.</p><p>2. <strong>Lifespan</strong>:<br>CTEs: Exist only during the execution of a single query.<br>Temporary Tables: Can exist beyond a single query execution.</p><p>3. <strong>Explicit Management:</strong><br>CTEs: Cannot be explicitly created, altered, or dropped.<br>Temporary Tables: Can be explicitly created, altered, or dropped.</p><p>4. <strong>Scope</strong>:<br> CTEs: Accessible only within the defining query.<br> Temporary Tables: Accessible by multiple queries within the same batch.</p><p><strong>When to Use CTEs vs. Temporary Tables:</strong></p><p><strong>CTEs:</strong> Preferred for simplifying complex queries, improving readability, and referencing the same result set multiple times within a query.</p><p><strong>Temporary Tables:</strong> Useful when handling and manipulating large data sets, requiring data reference across multiple queries in the same batch. They can enhance query performance through indexing and optimization.</p><h3><strong>Conclusion:</strong></h3><p>CTEs and Temporary Tables are valuable in SQL Server, each with its strengths. Understanding their distinctions in storage, lifespan, and management allows you to select the appropriate tool for optimizing queries in terms of performance and readability.</p><p>Connect with us on <a href="https://www.youtube.com/channel/UCOuQ1UMKMKGEey4-fxQVSCQ"><strong><em>YouTube</em> </strong></a>to get more such updates.<br>Follow us on <a href="https://www.instagram.com/the_dataholics/"><strong><em>Instagram </em></strong></a>page to get more information on <strong>Data Science &amp; Analytics.</strong></p><p><strong>Get top SQL interview questions for data/business analyst </strong><a href="https://medium.com/@akshatagrawal9431/mastering-advanced-sql-practical-interview-questions-for-2024-94de0d3e0bdb"><strong>here</strong></a><strong>.</strong></p><p><strong>For end to end SQL Portfolio Project (Pizza Case Study) click </strong><a href="https://medium.com/@akshatagrawal9431/sql-pizza-case-study-project-portfolio-5a8afa69e265"><strong><em>here</em></strong></a><strong><em>.</em></strong></p><p><strong>To practice SQL, click </strong><a href="https://medium.com/@akshatagrawal9431/week-4-data-bank-sql-solutions-ac8f25905fc2"><strong><em>here </em></strong></a>to <strong>read my other blog in which I have shared solutions of Week 4 SQL challenge from 8 week SQL Challenge.</strong></p><p><strong>Understand the order of execution in SQL by clicking </strong><a href="https://medium.com/@akshatagrawal9431/sql-order-of-execution-82b595ef4e8b"><strong><em>here</em></strong></a><strong><em>.</em></strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1072353224d4" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Week 4 Data Bank SQL Solutions]]></title>
            <link>https://medium.com/@akshatagrawal9431/week-4-data-bank-sql-solutions-ac8f25905fc2?source=rss-a244e194dfaf------2</link>
            <guid isPermaLink="false">https://medium.com/p/ac8f25905fc2</guid>
            <dc:creator><![CDATA[Akshat Agrawal]]></dc:creator>
            <pubDate>Sat, 09 Sep 2023 07:55:04 GMT</pubDate>
            <atom:updated>2024-06-13T20:36:14.104Z</atom:updated>
            <content:encoded><![CDATA[<h4>8 Week SQL Challenge</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Xe1DnZkK15YZhPtzhX7-UQ.png" /></figure><p>Click <a href="https://8weeksqlchallenge.com/case-study-4/">here </a>for the detailed Problem Statement and DDL commands. Once you have created Database and Tables, let’s move on to the questions.</p><p>use eight_week_sql_challenge; — database name</p><p>select * from customer_nodes; — 3500 records</p><p>select * from customer_transactions; — 5868 records</p><p>select * from regions;</p><p>— select * from customer_nodes order by customer_id</p><h4>Section A solutions:</h4><h4>— A. Customer Nodes Exploration</h4><p>/*</p><p>How many unique nodes are there on the Data Bank system?</p><p>What is the number of nodes per region?</p><p>How many customers are allocated to each region?</p><p>How many days on average are customers reallocated to a different node?</p><p>What is the median, 80th and 95th percentile for this same reallocation days metric for each region?</p><p>*/</p><p>— How many unique nodes are there on the Data Bank system?</p><p>select count(distinct node_id) from customer_nodes;</p><p>— What is the number of nodes per region?</p><p>select region_id, count(node_id) as node_count</p><p>from customer_nodes</p><p>group by region_id</p><p>— How many customers are allocated to each region?</p><p>select r.region_id, r.region_name, count(distinct customer_id) as customer_count</p><p>from customer_nodes c join regions r on c.region_id=r.region_id</p><p>group by r.region_id, r.region_name</p><p>— How many days on average are customers reallocated to a different node?</p><p>select * from customer_nodes where customer_id = 1</p><p>select *, DATEDIFF(DAY, start_date, end_date) as days</p><p>into #t1</p><p>from customer_nodes where — customer_id = 1 and</p><p>year(end_date) != 9999</p><p>order by customer_id, start_date</p><p>select * from #t1 order by customer_id — where customer_id = 1</p><p>select customer_id, node_id, sum(days) as sum_ into #t2 from #t1 group by customer_id, node_id</p><p>select customer_id, AVG(sum_) as avg_days from #t2 group by customer_id order by customer_id</p><p>select avg(sum_) from #t2</p><p>drop table #t1</p><p>drop table #t2</p><p>— What is the median, 80th and 95th percentile for this same reallocation days metric for each region?</p><p>;with reallocation as</p><p>(</p><p>select r.region_id,</p><p>region_name,</p><p>DATEDIFF(D, start_date, end_date) day_diff</p><p>from customer_nodes c join regions r</p><p>on r.region_id=c.region_id</p><p>where end_date != ‘99991231’)</p><p>select distinct</p><p>region_id,</p><p>region_name,</p><p>PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY day_diff)</p><p>OVER (PARTITION BY region_name) AS median,</p><p>PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY day_diff)</p><p>OVER (PARTITION BY region_name) AS percentile_80,</p><p>PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY day_diff)</p><p>OVER (PARTITION BY region_name) AS percentile_90</p><p>from reallocation</p><p>order by region_id;</p><h4>Section B solutions:</h4><p>/*</p><p>B. Customer Transactions</p><p>What is the unique count and total amount for each transaction type?</p><p>What is the average total historical deposit counts and amounts for all customers?</p><p>For each month — how many Data Bank customers make more than 1 deposit and either 1 purchase or 1 withdrawal in a single month?</p><p>What is the closing balance for each customer at the end of the month?</p><p>What is the percentage of customers who increase their closing balance by more than 5%?</p><p>*/</p><p>select * from customer_nodes</p><p>select * from customer_transactions</p><p>— What is the unique count and total amount for each transaction type?</p><p>select txn_type, count(*) as count_transactions, sum(txn_amount) as total_amount</p><p>— instead of *, if transaction_id was present,it was goot as * can also count null values</p><p>from customer_transactions</p><p>group by txn_type</p><p>— What is the average total historical deposit counts and amounts for all customers?</p><p>select * from customer_transactions;</p><p>with cte as (</p><p>select customer_id, count(*) as count_transactions, sum(txn_amount) as total_amount</p><p>from customer_transactions where txn_type = ‘deposit’</p><p>group by customer_id</p><p>)</p><p>select avg(count_transactions), avg(total_amount) from cte</p><p>— For each month — how many Data Bank customers make more than 1 deposit and either 1 purchase or 1 withdrawal in a single month?</p><p>select customer_id, month(txn_date) as month,</p><p>sum(case when txn_type = ‘deposit’ then 1 else 0 end) as deposit_count,</p><p>sum(case when txn_type = ‘withdrawal’ then 1 else 0 end) as count_withdrwal,</p><p>sum(case when txn_type = ‘purchase’ then 1 else 0 end) as count_purchase</p><p>into #t_1</p><p>from customer_transactions</p><p>group by customer_id, month(txn_date)</p><p>select month, count(customer_id) as count_customers</p><p>from #t_1</p><p>where deposit_count&gt;1 and (count_withdrwal=1 or count_purchase=1)</p><p>group by month</p><p>drop table #t_1;</p><p>— alternative</p><p>with abc as</p><p>(</p><p>select customer_id, datepart(month,txn_date) as months,</p><p>sum(case when txn_type=’deposit’ then 1 else 0 end) as deposit,</p><p>sum(case when txn_type=’withdrawal’ then 1 else 0 end) as withdrawl,</p><p>sum(case when txn_type=’purchase’ then 1 else 0 end) as purchase</p><p>from customer_transactions</p><p>group by datepart(month,txn_date),customer_id</p><p>)</p><p>select months,count(customer_id) as customers</p><p>from abc</p><p>where deposit&gt;1 and (withdrawl=1 or purchase=1)</p><p>group by months;</p><p>— What is the closing balance for each customer at the end of the month?</p><p>WITH CTE AS (</p><p>SELECT customer_id, MONTH(txn_date) AS months,</p><p>SUM(CASE WHEN txn_type = ‘deposit’ THEN txn_amount ELSE -txn_amount END) AS balance</p><p>FROM customer_transactions</p><p>— WHERE customer_id IN (1, 2,3)</p><p>GROUP BY customer_id, MONTH(txn_date)</p><p>)</p><p>— select * from cte order by customer_id, months</p><p>SELECT customer_id, months, balance, SUM(balance) OVER (PARTITION BY customer_id ORDER BY months ASC) AS Closing_balance</p><p>FROM CTE</p><p>ORDER BY customer_id, months;</p><p>— Alternative</p><p>;with abc as (</p><p>select customer_id ,datepart(month,txn_date) as months,</p><p>sum(case when txn_type=’deposit’ then txn_amount</p><p>else -txn_amount</p><p>end) as balance</p><p>from customer_transactions</p><p>group by customer_id,datepart(month,txn_date)</p><p>— order by 1</p><p>)</p><p>select *,</p><p>sum(balance) over(partition by customer_id order by months asc rows between unbounded preceding and current row) as Closing_balance</p><p>from abc — where customer_id in (1,2)</p><p>group by customer_id, months, balance</p><p>order by customer_id</p><p>— What is the percentage of customers who increase their closing balance by more than 5%?</p><p>;with abc as (</p><p>select customer_id ,datepart(month,txn_date) as months,</p><p>sum(case when txn_type=’deposit’ then txn_amount</p><p>else -txn_amount</p><p>end) as balance</p><p>from customer_transactions</p><p>group by customer_id,datepart(month,txn_date)</p><p>— order by 1</p><p>),</p><p>close_bal as (</p><p>select *, sum(balance) over(partition by customer_id order by months asc rows between unbounded preceding and current row) as Closing_balance</p><p>from abc</p><p>group by customer_id, months, balance</p><p>),</p><p>prev_bal as (</p><p>select *, lag(Closing_balance)over(partition by customer_id order by months) as prev_bal</p><p>from close_bal</p><p>),</p><p>bal_diff as(</p><p>select *,</p><p>case when Closing_balance&gt;0 and prev_bal&gt;0 then Closing_balance-prev_bal</p><p>when Closing_balance&lt;0 and prev_bal&lt;0 then Closing_balance-prev_bal</p><p>when Closing_balance&lt;0 and prev_bal&gt;0 then -(-Closing_balance+prev_bal)</p><p>when Closing_balance&gt;0 and prev_bal&lt;0 then (Closing_balance-prev_bal)</p><p>end as bal_diff</p><p>from prev_bal</p><p>where Closing_balance &gt; prev_bal</p><p>— order by customer_id</p><p>),</p><p>bal as(</p><p>select *,cast (bal_diff*100 /prev_bal as float) as bal_prcnt</p><p>from bal_diff</p><p>)</p><p>select round(cast(count(distinct customer_id)*100/(select count(distinct customer_id) from customer_transactions) as float),2) as prcnt_customer</p><p>from bal</p><p>where bal_prcnt&gt;5 or bal_prcnt&lt;-5</p><p>— trying for a short alternative</p><p>WITH CTE AS (</p><p>SELECT customer_id, MONTH(txn_date) AS months,</p><p>SUM(CASE WHEN txn_type = ‘deposit’ THEN txn_amount ELSE -txn_amount END) AS balance</p><p>FROM customer_transactions</p><p>— WHERE customer_id IN (1, 2,3)</p><p>GROUP BY customer_id, MONTH(txn_date)</p><p>)</p><p>— select * from cte order by customer_id, months</p><p>SELECT customer_id, months, SUM(balance) OVER (PARTITION BY customer_id ORDER BY months ASC) AS Closing_balance</p><p>into #t_1 FROM CTE</p><p>ORDER BY customer_id, months;</p><p>SELECT customer_id, months, Closing_balance,</p><p>Closing_balance + isnull(lag(Closing_balance) over (partition by customer_id order by months),0) as diff</p><p>from #t_1</p><p>drop table #t_1</p><p>Thanks for reading till here.</p><p>For any feedback or improvements please write on akshatagrawal169@gmail.com</p><p>Connect with us on <a href="https://www.youtube.com/channel/UCOuQ1UMKMKGEey4-fxQVSCQ"><strong><em>YouTube</em> </strong></a>to get more such updates.<br>Follow us on <a href="https://www.instagram.com/the_dataholics/"><strong><em>Instagram </em></strong></a>page to get more information on <strong>Data Science &amp; Analytics.</strong></p><blockquote><strong>Get top SQL interview questions for data/business analyst </strong><a href="https://medium.com/@akshatagrawal9431/mastering-advanced-sql-practical-interview-questions-for-2024-94de0d3e0bdb"><strong>here</strong></a><strong>.</strong></blockquote><blockquote><strong>For end to end SQL Portfolio Project (Pizza Case Study) click </strong><a href="https://medium.com/@akshatagrawal9431/sql-pizza-case-study-project-portfolio-5a8afa69e265"><strong><em>here</em></strong></a><strong><em>.</em></strong></blockquote><blockquote><strong>If you want to understand the concept of CTEs vs Temporary tables in SQL, click </strong><a href="https://medium.com/@akshatagrawal9431/temporary-tables-and-common-table-expression-ctes-in-sql-1072353224d4"><strong><em>here </em></strong></a><strong>read my other blog in which I have the difference and use cases of them.</strong></blockquote><blockquote><strong>Understand the order of execution in SQL by clicking </strong><a href="https://medium.com/@akshatagrawal9431/sql-order-of-execution-82b595ef4e8b"><strong><em>here</em></strong></a><strong><em>.</em></strong></blockquote><p><strong><em>To understand SQL Business Analyst Interview Question click </em></strong><a href="https://medium.com/@akshatagrawal9431/sql-business-analyst-interview-question-intermediate-level-29aa59e81692"><strong><em>here</em></strong></a><strong><em>.</em></strong></p><p><strong><em>To understand SQL Order of Execution click </em></strong><a href="https://medium.com/@akshatagrawal9431/sql-order-of-execution-82b595ef4e8b"><strong><em>here</em></strong></a><strong><em>.</em></strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ac8f25905fc2" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>