<?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 Abdullah Zunorain on Medium]]></title>
        <description><![CDATA[Stories by Abdullah Zunorain on Medium]]></description>
        <link>https://medium.com/@abdullahzunorain?source=rss-2e9c562555a5------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*aGDosF4wQI1cWHpxFHeaiw.jpeg</url>
            <title>Stories by Abdullah Zunorain on Medium</title>
            <link>https://medium.com/@abdullahzunorain?source=rss-2e9c562555a5------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 11:33:01 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@abdullahzunorain/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[Every Way to Deploy LLM / & ML Models on AWS — An Engineer’s Complete Guide]]></title>
            <link>https://abdullahzunorain.medium.com/every-way-to-deploy-llm-ml-models-on-aws-an-engineers-complete-guide-c1e8a14ebb49?source=rss-2e9c562555a5------2</link>
            <guid isPermaLink="false">https://medium.com/p/c1e8a14ebb49</guid>
            <category><![CDATA[llm]]></category>
            <category><![CDATA[mlops]]></category>
            <category><![CDATA[ai]]></category>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[devops]]></category>
            <dc:creator><![CDATA[Abdullah Zunorain]]></dc:creator>
            <pubDate>Wed, 22 Apr 2026 03:26:57 GMT</pubDate>
            <atom:updated>2026-04-22T03:26:57.916Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yXXYCuVTBaT-03MTWY0sFQ.png" /><figcaption>Way to Deploy LLM / &amp; ML Models on AWS</figcaption></figure><p><em>A technical breakdown of all 11 deployment paths across 3 tiers, with real-world tradeoffs, decision frameworks, and the opinions no one else will say out loud.</em></p><h3>Why This Article Exists</h3><p>When I started working at the intersection of ML engineering and cloud infrastructure, one thing became clear fast: <strong>most AWS ML content on the internet is either too shallow or too scattered.</strong></p><p>You get marketing pages that say “just use Bedrock!” without explaining when you shouldn’t. Or deep SageMaker docs that assume you already know what inference mode you need. Or blog posts that list options without telling you the real tradeoffs.</p><p>This article is my attempt to fix that — written from the perspective of someone who has worked across both ML engineering and cloud/DevOps, and who has had to make these architecture decisions for real systems.</p><p>By the end, you’ll know:</p><ul><li>Every way AWS lets you deploy or consume ML and LLM models</li><li>The honest tradeoffs of each option</li><li>How to pick the right one for your specific situation</li><li>The mental model that ties it all together</li></ul><p>Let’s get into it.</p><h3>The Mental Model First</h3><p>Before listing options, you need the right frame.</p><p>AWS ML deployment isn’t a flat list of tools. It’s a <strong>spectrum of control vs. convenience</strong>, organized into three distinct tiers:</p><blockquote><em>🟦 </em><strong><em>Tier A (Bedrock)</em></strong><em> — You call models. AWS does literally everything else.</em></blockquote><blockquote><em>🟨 </em><strong><em>Tier B (SageMaker)</em></strong><em> — You bring models. AWS manages the infrastructure.</em></blockquote><blockquote><em>🟥 </em><strong><em>Tier C (Raw Compute)</em></strong><em> — You bring models AND manage the infrastructure yourself.</em></blockquote><p>Every option on AWS fits cleanly into one of these three tiers. Once you internalize this, every architecture decision becomes significantly easier.</p><p>Now let’s go through all 11 options.</p><h3>🟦 TIER A — Amazon Bedrock</h3><h3>No infrastructure. No deployment. Just call the model.</h3><p>Bedrock is AWS’s managed foundation model API platform. You don’t host anything. You don’t configure anything. AWS maintains a fleet of state-of-the-art models — Claude, Llama, Mistral, Amazon Nova, Cohere, Stability AI — and you access them via a simple API call.</p><p>This is the right starting point for most teams, and the wrong long-term home for some of them. Here’s why.</p><h3>1. Bedrock On-Demand</h3><p><strong>What it is:</strong> The simplest possible way to use a large language model on AWS. You send a request, you get a response, you pay per token. No setup. No instances. No configuration.</p><p><strong>How it works:</strong> A single API call via boto3 or any AWS SDK hits the Bedrock runtime endpoint. You choose the model, pass your prompt, and receive a response. AWS handles all the infrastructure, scaling, and availability invisibly.</p><p><strong>When to use it:</strong> Prototyping, development environments, applications with unpredictable or low traffic, and any use case where simplicity matters more than cost optimization. If you’re building your first LLM-powered feature, start here.</p><p><strong>The honest tradeoff:</strong> On-Demand is convenient, but it’s not the cheapest option at scale. At high, consistent volumes, the per-token cost compounds quickly. It also shares capacity with other AWS customers, which means you can get throttled during peak periods if you haven’t negotiated limits.</p><p><strong>My opinion:</strong> On-Demand is the right default. Build on it. Prove your use case. Then evaluate whether you need something more predictable or cost-efficient.</p><h3>2. Bedrock Batch Inference</h3><p><strong>What it is:</strong> Instead of calling the model one request at a time, you upload an entire file of prompts to S3, submit a single job, and AWS processes everything asynchronously — typically within 24 hours — at approximately 50% less cost than On-Demand.</p><p><strong>How it works:</strong> You create a JSONL file with all your prompts, point Bedrock at the S3 location, and submit a CreateModelInvocationJob API call. AWS processes the batch and writes results back to an S3 output path you specify.</p><p><strong>When to use it:</strong> Bulk document processing, data enrichment pipelines, evaluating model outputs across large datasets, generating embeddings at scale, any offline workload where latency doesn’t matter but cost and volume do.</p><p><strong>The honest tradeoff:</strong> You give up real-time responses entirely. This is not for user-facing features. It’s an overnight pipeline, not an API. If your use case has any latency requirement, this option doesn’t exist for you.</p><p><strong>My opinion:</strong> Batch inference is far more underutilized than it should be. If you’re running nightly ML enrichment jobs and still paying On-Demand rates, you’re leaving roughly half your inference budget on the table for no reason.</p><h3>3. Bedrock Provisioned Throughput</h3><p><strong>What it is:</strong> You reserve dedicated model capacity at a fixed hourly rate, committing to either a 1-month or 6-month term. In exchange, you get guaranteed throughput, no throttling, and predictable pricing.</p><p><strong>How it works:</strong> You call CreateProvisionedModelThroughput to reserve one or more Model Units (MUs) for a specific model. Each MU represents a unit of throughput. Your application then routes requests to your provisioned endpoint instead of the shared On-Demand pool.</p><p><strong>When to use it:</strong> High-traffic production applications where you need guaranteed availability and consistent latency. If your app is live and serving real users at scale, On-Demand throttling is a production incident waiting to happen.</p><p><strong>The honest tradeoff:</strong> You pay the hourly rate whether you use it or not. The economics only make sense if you’re running at roughly 50–70% utilization or higher. Below that, On-Demand is cheaper. This requires you to actually know your traffic patterns before committing.</p><p><strong>My opinion:</strong> Provisioned Throughput is the sign of a mature production deployment. If you’re running a serious LLM-powered product on Bedrock and you haven’t evaluated this option, you either have very low traffic or you’re not optimizing your infrastructure costs seriously enough.</p><h3>🟨 TIER B — Amazon SageMaker AI</h3><h3>You bring the model. AWS manages the platform.</h3><p>SageMaker is where professional ML engineering lives on AWS. You have a trained model — whether you built it yourself, fine-tuned an open-source one, or pulled it from Hugging Face — and you need to serve it reliably. SageMaker takes that model artifact and handles the hosting, scaling, monitoring, and operations.</p><p>AWS officially defines exactly four inference options within SageMaker. These are not just product features — they represent four fundamentally different architectural patterns for model serving.</p><h3>4. Real-Time Inference</h3><p><strong>What it is:</strong> A persistent, always-on model endpoint running on a dedicated compute instance. Every request receives a synchronous response in under 100 milliseconds. The endpoint is live 24/7.</p><p><strong>How it works:</strong> You package your model, define your serving logic, and deploy to a managed endpoint. SageMaker handles instance provisioning, health checks, rolling updates, and auto-scaling behind the scenes. You get a stable HTTPS endpoint you can call from any service.</p><p><strong>When to use it:</strong> Any user-facing feature requiring real-time responses. Chatbots, recommendation engines, fraud detection, content moderation, real-time personalization. If a human is waiting for the response, this is your option.</p><p><strong>The honest tradeoff:</strong> You pay for the instance whether it’s receiving traffic or not. An idle ml.g4dn.xlarge costs the same whether it’s processing 1,000 requests per minute or zero. For low or unpredictable traffic, this is expensive.</p><p><strong>My opinion:</strong> Real-Time Inference is the backbone of production ML serving. Most serious ML-powered features end up here. The cost concern is real, but if you have consistent traffic, the cost is predictable and worth it for the performance guarantees.</p><h3>5. Serverless Inference</h3><p><strong>What it is:</strong> A SageMaker endpoint with no persistent instances. When traffic arrives, SageMaker provisions compute automatically. When traffic disappears, it scales to zero. You pay only for the milliseconds your model is actually running.</p><p><strong>How it works:</strong> You configure a ServerlessInferenceConfig with memory allocation and maximum concurrency. SageMaker handles the rest. The endpoint exists as a logical resource, but compute is only allocated on demand.</p><p><strong>When to use it:</strong> Development and staging environments, internal tools, ML features with unpredictable or low traffic, use cases where you want ML capabilities without committing to an always-on instance.</p><p><strong>The honest tradeoff:</strong> Cold starts. When traffic arrives after a period of inactivity, the first request experiences a latency spike while compute is provisioned. For latency-sensitive applications, this is unacceptable. Payload size is also capped at 4 MB and inference time at 60 seconds.</p><p><strong>My opinion:</strong> Serverless Inference is extremely useful and extremely misunderstood. It is not a production solution for user-facing features — cold starts make it unsuitable. But for internal APIs, batch-adjacent pipelines, and development workloads, it’s an elegant way to eliminate idle compute costs.</p><h3>6. Asynchronous Inference</h3><p><strong>What it is:</strong> A queue-based inference pattern. You submit a request, receive a job ID, and poll S3 for the result when it’s ready. Supports payloads up to 1 GB and processing times up to 1 hour. The endpoint can also scale to zero between jobs.</p><p><strong>How it works:</strong> You configure an AsyncInferenceConfig with an output S3 path. Requests go into a managed queue. SageMaker processes them as capacity allows and writes results to S3. You can optionally configure SNS notifications when jobs complete.</p><p><strong>When to use it:</strong> Workloads with large inputs or long processing times — video analysis, large document processing, multi-modal inference, complex model pipelines. Any use case where “near real-time” is acceptable but “immediately” is not required.</p><p><strong>The honest tradeoff:</strong> This is not for interactive applications. The response time includes queue wait time plus processing time. You’re designing for asynchronous workflows, which requires a fundamentally different application architecture than synchronous API calls.</p><p><strong>My opinion:</strong> Async Inference is the right answer for a class of problems that engineers often try to force into Real-Time Inference and then wonder why their latency is bad. Large document analysis, multi-page OCR, video processing — these belong in async pipelines, not synchronous endpoints.</p><h3>7. Batch Transform</h3><p><strong>What it is:</strong> A fully offline processing job with no persistent endpoint. You point SageMaker at an S3 dataset, it provisions compute, processes every record, writes results back to S3, and tears down the infrastructure when done. Nothing keeps running after the job completes.</p><p><strong>How it works:</strong> You define a Transformer with your model, instance type, and I/O S3 paths, and call .transform(). SageMaker handles all the compute lifecycle. The job runs, finishes, and you&#39;re charged only for the compute time used.</p><p><strong>When to use it:</strong> Nightly scoring pipelines, backfilling predictions on historical data, generating embeddings for an entire dataset, one-time large-scale model evaluation runs.</p><p><strong>The honest tradeoff:</strong> There is no endpoint to call. This is a job, not a service. It requires upstream and downstream systems that can work with S3-based I/O, which means orchestration via Step Functions, Airflow, or similar tools.</p><p><strong>My opinion:</strong> Batch Transform is the cleanest pattern for offline ML workloads and the most underappreciated option in SageMaker. If you’re spinning up a persistent endpoint to run a nightly batch job and then leaving it running, you are wasting money and adding operational complexity for no reason.</p><h3>🟥 TIER C — Raw Compute</h3><h3>You bring the model AND manage the infrastructure. Full control. Full responsibility.</h3><p>Tier C is for teams who have outgrown managed platforms or need capabilities that managed platforms can’t provide. You’re working directly with AWS compute primitives, installing your own model serving runtimes, and owning your own scaling, monitoring, and operations.</p><p>More work. More responsibility. But also maximum flexibility and often the lowest cost per inference at scale.</p><h3>8. Amazon EC2</h3><p><strong>What it is:</strong> A virtual machine you rent from AWS and configure entirely yourself. You choose the hardware — GPU instances for training and inference (g5, p4), Inferentia chips (inf2) for cost-efficient inference, Trainium chips (trn1) for training — install your preferred runtime (vLLM, Triton Inference Server, PyTorch Serve), and run your model however you want.</p><p><strong>When to use it:</strong> Maximum control scenarios. Custom serving stacks. Heavy optimization for cost or latency. Teams running large-scale inference where the per-request economics of managed services become prohibitive.</p><p><strong>The honest tradeoff:</strong> You own the OS, the runtime, the scaling logic, the load balancer, the monitoring, the security patches, and the on-call pager when something breaks at 3am. This is not “set it and forget it” infrastructure.</p><p><strong>My opinion:</strong> EC2 is where the best ML infrastructure engineers live and where the most expensive mistakes happen. The cost savings at scale are real — sometimes dramatically real — but you need the engineering capacity to justify the operational overhead. Don’t go here because it sounds powerful. Go here because you’ve hit the ceiling of managed options.</p><h3>9. AWS Lambda</h3><p><strong>What it is:</strong> Serverless functions where you package a small model into a Docker container (up to 10 GB) and run it on a per-request basis. No idle cost whatsoever — you pay only when a request executes.</p><p><strong>When to use it:</strong> Small or heavily quantized models, lightweight classification or embedding tasks, event-driven ML pipelines, extremely low traffic workloads where any idle instance cost is unjustifiable.</p><p><strong>The honest tradeoff:</strong> Lambda has hard constraints that make it unsuitable for most serious ML workloads — memory limits, timeout limits, and cold start latency. Running a 7B parameter LLM on Lambda is not a reasonable architecture choice. This is for small, fast, CPU-friendly models.</p><p><strong>My opinion:</strong> Lambda has a real but narrow role in ML architectures — primarily as a lightweight orchestrator or preprocessor, not as a primary inference runtime. If your model fits and your latency tolerance is flexible, it’s an elegant serverless option. If not, don’t force it.</p><h3>10. Amazon ECS / Fargate</h3><p><strong>What it is:</strong> Container orchestration without Kubernetes. You package your model server into a Docker container, push it to ECR (Elastic Container Registry), and run it as a managed service. With Fargate, you don’t even manage the underlying EC2 instances — AWS does that for you.</p><p><strong>When to use it:</strong> Teams that have containerized their inference services and want autoscaling and load balancing without the operational complexity of Kubernetes. A middle path between raw EC2 and full EKS.</p><p><strong>The honest tradeoff:</strong> ECS has less flexibility than Kubernetes for advanced ML serving patterns — multi-model routing, canary deployments, GPU-aware scheduling. For simple containerized serving, it’s excellent. For complex multi-model orchestration, you’ll hit its limits.</p><p><strong>My opinion:</strong> ECS/Fargate is the most underrated option in Tier C. It gives you most of the benefits of containerized serving with significantly less operational overhead than EKS. If your team isn’t already Kubernetes-native, ECS is almost certainly the right choice over EKS.</p><h3>11. Amazon EKS (Kubernetes)</h3><p><strong>What it is:</strong> A fully managed Kubernetes cluster on AWS. The standard platform for enterprise-scale ML serving. Typically paired with frameworks like KServe, vLLM, Ray Serve, or Text Generation Inference (TGI) for model serving, and Karpenter for automatic GPU autoscaling.</p><p><strong>When to use it:</strong> Large engineering teams, multi-model serving at scale, complex traffic routing requirements, organizations that are already Kubernetes-native across their stack, or use cases that need advanced features like A/B testing between models, canary rollouts, and multi-region serving.</p><p><strong>The honest tradeoff:</strong> Kubernetes is powerful and famously complex. Operating a GPU-backed EKS cluster for ML serving is serious infrastructure work. The learning curve is steep, the failure modes are subtle, and the operational surface area is large. Don’t choose EKS because you’ve heard it’s the professional option. Choose it because you actually need what it provides.</p><p><strong>My opinion:</strong> EKS is the endgame for large-scale ML infrastructure, but it’s overkill for most teams at most stages. I’ve seen startups choose EKS for ML serving because it felt like the “serious” choice, and then spend 80% of their ML engineering time managing Kubernetes instead of improving their models. Know what you actually need before you go here.</p><h3>🎁 Bonus — Pre-Built AI Services</h3><h3>Don’t want to deal with models at all? Just call the API.</h3><p>AWS offers a suite of purpose-built AI APIs where the model is already trained, hosted, and maintained. You call an API endpoint and get a structured result. No model management. No infrastructure. No ML expertise required.</p><p>ServiceCapabilityAmazon RekognitionImage and video analysis, face detectionAmazon ComprehendText classification, entity recognition, sentimentAmazon TextractDocument text and data extractionAmazon TranscribeSpeech-to-textAmazon TranslateLanguage translationAmazon PollyText-to-speechAmazon ForecastTime-series predictionAmazon PersonalizeRecommendation engines</p><p><strong>When to use them:</strong> When you need a specific AI capability quickly, don’t have ML expertise on the team, and the task is well-defined and common enough that a pre-built model is likely to perform well.</p><p><strong>The honest tradeoff:</strong> These services give you no control over the underlying model. You can’t fine-tune them, you can’t inspect them, and you can’t optimize them for your specific domain. For general tasks, they’re excellent. For specialized domains, they often underperform custom solutions.</p><h3>The Principle That Ties It All Together</h3><p>Here’s the single most important piece of engineering judgment I can offer on this topic:</p><p><strong>Don’t optimize for sophistication. Optimize for fit.</strong></p><p>The most common mistake I see ML engineers and cloud architects make is choosing infrastructure based on what sounds impressive rather than what the problem actually requires. EKS is not inherently better than SageMaker. SageMaker is not inherently better than Bedrock. The right answer depends entirely on your traffic patterns, your team’s operational capacity, your latency requirements, and your cost constraints.</p><p>Start simple. Prove your use case. Add complexity only when a specific constraint forces you to.</p><p>The engineers who build the most reliable ML systems aren’t the ones who chose the most powerful infrastructure. They’re the ones who chose the most appropriate infrastructure — and then executed it well.</p><h3>Final Thoughts</h3><p>AWS gives you 11 distinct ways to deploy and use ML and LLM models. Each one exists because a real class of problems needs exactly what it offers. None of them is universally right or universally wrong.</p><p>The value of understanding all of them is not that you’ll use all of them. It’s that you’ll be able to look at any ML deployment problem and immediately know which options are relevant, which are off the table, and which deserves a deeper look.</p><p>That’s what separates ML engineers who build systems that last from ML engineers who build systems that work once and then become technical debt.</p><p>Build intentionally. Choose deliberately. And when in doubt, start with the simplest option that meets your requirements — because complexity has a cost that shows up long after the architecture diagram is drawn.</p><p><em>If you found this useful, share it with someone making AWS ML architecture decisions. And if you disagree with any of my opinions — I’d genuinely love to hear why in the comments.</em></p><p><strong>Tags:</strong> #AWS #MachineLearning #LLM #GenerativeAI #MLEngineering #CloudArchitecture #AmazonBedrock #SageMaker #MLOps #AIEngineering #AWSArchitecture #DeepLearning #CloudEngineering #AIInfrastructure #TechLeadership</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c1e8a14ebb49" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[A Comprehensive Overview of Object Detection]]></title>
            <link>https://abdullahzunorain.medium.com/a-comprehensive-overview-of-object-detection-146462f1d7c9?source=rss-2e9c562555a5------2</link>
            <guid isPermaLink="false">https://medium.com/p/146462f1d7c9</guid>
            <category><![CDATA[computer-vision]]></category>
            <category><![CDATA[object-detection]]></category>
            <category><![CDATA[ai]]></category>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[deep-learning]]></category>
            <dc:creator><![CDATA[Abdullah Zunorain]]></dc:creator>
            <pubDate>Wed, 15 Jan 2025 17:01:38 GMT</pubDate>
            <atom:updated>2025-01-16T15:37:57.356Z</atom:updated>
            <content:encoded><![CDATA[<h3>Introduction</h3><p>Object detection is one of the most exciting technologies in the rapidly evolving field of <a href="https://www.neilsahota.com/what-is-computer-vision-explanation-types-examples/">computer vision</a>, a subbranch of <a href="https://research.aimultiple.com/ai/">AI</a>.</p><p>Giving machines superhero spectacles to see and comprehend the world like humans is analogous. <em>However, rather than merely identifying an object, it provides information about </em><strong><em>what it is</em></strong><em> and the </em><strong><em>precise location of its presence</em></strong><em> in a picture or video. </em>It is the foundation for many applications, including security monitoring, driverless cars, and medical imaging.</p><h3>Object Detection Overview</h3><p>Have you ever wondered how computers recognize and find things in a given scene? This article aims to address this question.</p><p>So, here we go!</p><p>Imagine this: As you scroll through your Instagram feed, you come upon a photo of a monkey in the park. Object detection is the technology that recognizes that monkey, highlights it with a box, and even identifies the breed it looks like. It goes further than traditional image classification, which merely determines whether a monkey is present, by determining the exact location of that monkey.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/928/1*8BEj6k2IPfkjHV5zCl-yGQ.png" /><figcaption><strong>Source</strong>:<a href="https://medium.com/@pmegne/introduction-to-object-detection-8be574b82d5c"> Introduction to Object Detection</a>, Medium article by Ornela Pmegne.</figcaption></figure><p>For more details… Explore the <a href="https://blog.roboflow.com/object-detection/#:~:text=Object%20detection%20is%20a%20computer%20vision%20solution%20that%20identifies%20objects,that%20a%20prediction%20is%20accurate.">Ultimate Guide to Roboflow.</a></p><h3>How Does Object Detection Work?</h3><h4>Core Structure of Object Detection Models</h4><p>Deep learning-based object detection models are generally composed of two main components:</p><ol><li><strong>Encoder</strong>:</li></ol><ul><li>This part processes the input image to extract features such as edges, textures, and patterns.</li><li>It uses convolutional layers, typically from pre-trained backbone networks like ResNet, MobileNet, or VGG.</li><li>These extracted features form a high-level representation of the input image.</li></ul><p><strong>2. Decoder</strong>:</p><ul><li>The decoder utilizes the features extracted by the encoder to predict bounding boxes and classify the objects.</li><li>Outputs include the coordinates of bounding boxes, object labels, and confidence scores for each detection.</li></ul><h4>Common Approaches to Object Detection</h4><h4>1. Pure Regression Models</h4><ul><li>These models directly predict the coordinates of bounding boxes and their class labels.</li><li>They are simple but have a significant limitation: the number of objects must be predefined.</li><li>Suitable for scenarios where the number of objects in an image is constant or predictable.</li></ul><h4>2. Region Proposal Networks (RPNs)</h4><ul><li>RPN-based models propose regions in the image that are likely to contain objects.</li><li>These regions are then processed by a classification sub-network to assign labels or reject false positives.</li><li>This method, employed in architectures like Faster R-CNN and Mask R-CNN, provides high accuracy but is computationally expensive.</li></ul><h4>3. Single Shot Detectors (SSDs)</h4><ul><li>SSDs divide the input image into a grid and assign multiple anchor boxes of varying sizes and shapes to each grid cell.</li><li>For each anchor box, predictions include object presence, class label, and adjustments to the box dimensions.</li><li>They are faster and more efficient than RPN-based models, making them ideal for real-time applications.</li><li>However, SSDs require post-processing like <strong>Non-Maximum Suppression (NMS)</strong> to eliminate redundant or overlapping detections.</li></ul><h4>4. CenterNet</h4><ul><li>CenterNet predicts the center point of an object and its dimensions (height and width) without relying on anchor boxes or region proposals.</li><li>It offers improved efficiency and accuracy compared to SSDs and R-CNNs.</li></ul><h4>Evaluation Metrics</h4><p>To measure the performance of object detection models, the following metrics are commonly used:</p><ul><li><strong>Intersection over Union (IoU)</strong>: Quantifies the overlap between the predicted and ground truth bounding boxes, with values ranging from 0 (no overlap) to 1 (perfect overlap).</li><li><strong>Accuracy of Classification</strong>: Calculates the percentage of correctly identified object labels.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/889/1*NiWVXUGaNrvc5Qt2vqZGpA.png" /><figcaption><strong>Source</strong>: <a href="https://project.inria.fr/bigvisdata/files/2020/01/cs231n_2019_lecture12_detection.pdf">Detecting a single object</a></figcaption></figure><h3>Types of Object Detection</h3><p>The magic is in object detection, which is a key component of computer vision.</p><p>But did you know that these gains are being driven by a variety of <a href="https://roboflow.com/model-task-type/object-detection">object detection models</a>? Let’s talk about them.</p><p>Object detection can be categorized into two main approaches:</p><ol><li><strong>Machine Learning-Based Approaches</strong><br>These traditional methods use computer vision techniques to analyze image features like color histograms or edges. By identifying patterns and groups of pixels, the system predicts object locations and labels using models like regression or classification algorithms.</li><li><strong>Deep Learning-Based Approaches</strong><br>These modern methods rely on convolutional neural networks (CNNs) for end-to-end object detection. Unlike traditional methods, they automatically extract and learn features directly from the data without requiring manual definition.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/764/1*WdEjJnvGlVQIgIaf7sT-eQ.png" /><figcaption><strong>Source: </strong><a href="https://www.researchgate.net/figure/Classification-of-various-object-detection-techniques_fig3_360126967">Classification of various object detection techniques</a></figcaption></figure><p>Since deep learning has become the state-of-the-art in object detection, this guide will primarily focus on these advanced techniques.</p><h3>Object Detection Architectures</h3><p>From simple algorithms to <a href="https://www.neilsahota.com/what-is-deep-learning-definition-and-techniques-with-examples/">deep learning</a> networks, learn how object detection architectures evolve to identify objects accurately.</p><p>Object detection architectures are grouped into <em>families based on how they function and what problems they aim to solve</em>.</p><p>Here are the main categories:</p><h4>Two-Stage Detectors: Accuracy First</h4><p>These models divide their tasks into two phases:</p><p>○ <strong><em>Region Proposal</em></strong><em>: Find regions of interest (ROIs) that might contain objects.</em></p><p>○ <strong><em>Classification</em></strong><em>: Analyze the RoIs to classify the objects inside.</em></p><p>● <strong>R-CNN (Region-Based Convolutional Neural Network)</strong>:<br> Think of R-CNN as a pioneer. It works by scanning the picture for probable item areas, cropping them, and processing them using a convolutional neural network (CNN).<br> Example: A car’s image is divided into chunks, and R-CNN determines which chunks have cars, people, or nothing.</p><p>● <strong>Fast R-CNN &amp; Faster R-CNN</strong>:<br> These are advanced versions of R-CNN. They are faster because they eliminate redundant steps, like cropping the image repeatedly. Faster R-CNN additionally incorporates a <em>Region Proposal Network (RPN)</em>, which proposes bounding boxes more effectively.<br> <em>Imagine scanning a bookshelf: You find sections with books first, then zoom in, rather than looking through every book.</em></p><p>● <strong>Mask R-CNN</strong>:<br> — <em>A superhero version of Faster R-CNN!</em></p><p>In addition to detecting objects, it also predicts the <em>shape of objects</em> (segmentation).</p><p>For example: not only detecting a cat but outlining its fluffy tail.</p><h4>Single-Stage Detectors: Speed is King</h4><p>Instead of splitting tasks into two steps, these models do everything in one go, making them faster but sometimes less precise.</p><p>● <strong>YOLO (You Only Look Once)</strong>:<br> YOLO processes the entire image in a single pass, partitioning it into a grid to pinpoint objects. This approach is akin to scanning a forest landscape and instantly spotting the larger birds, although the tiny ones might be overlooked. YOLO’s swift detection of prominent objects makes it a valuable tool.</p><p>○ <strong>YOLOv2 &amp; YOLOv3</strong>:<br> These versions improved accuracy by adding more anchor boxes (for better bounding boxes) and a deeper neural network. YOLOv3 became a community favorite for being fast and reasonably accurate.</p><p>○ <strong>YOLOv4 &amp; YOLOv5</strong>:<br> Both employ mosaic augmentation, a data augmentation method. In this method, the model merges portions of several images into a single image during training. These are widely used today in apps like autonomous driving.</p><p>YOLOv5 and YOLOv8, which are developed and maintained by the Ultralytics team, are power production object detection models that are currently in use worldwide.</p><p><strong><em>Want to know more about YOLO?</em></strong></p><p><a href="https://blog.roboflow.com/guide-to-yolo-models/"><em>Check out the thorough overview of the YOLO family of models</em></a><em>. It includes information on everything from the YOLO11 model of today to the initial YOLO model–which signaled the start of the architecture.</em></p><p>● <strong>SSD (Single Shot Multibox Detector)</strong>:<br> Rather than splitting pictures into grids, SSD detects things at various sizes using attributes from multiple levels of the network. That is why SSD is so effective at recognizing both large and small things.</p><p>For instance, identifying an elephant and a mouse simultaneously in the same photograph.</p><h4>Transformer-Based Models: The Game Changers</h4><p>Transformers revolutionized natural language processing, and now they’re shaking up object detection.</p><p>● <strong>DETR (Detection Transformer)</strong>:<br> <a href="https://zilliz.com/learn/detection-transformers-detr-end-to-end-object-detection-with-transformers">DETR</a> views the image as a whole and focuses on relationships between image parts, making it more flexible and adaptive. It doesn’t rely on predefined rules like anchor boxes.<a href="#_msocom_13">[13]</a> <br> Example: Detecting a cat and understanding its context within a couch scene.</p><h4>Region-Free Models: Breaking Rules</h4><p>These models skip the traditional <em>region proposal step</em> altogether and directly predict object locations and bounding boxes.</p><p>● <strong>CenterNet</strong>:<br> Detects objects using their center points rather than <a href="https://medium.com/@rajdeepsingh/a-quick-reference-for-bounding-boxes-in-object-detection-f02119ddb76b">bounding box</a> corners. It’s like marking the exact spot where an object sits in the image.<br> Example: Pinpointing where the basketball is on the court in a photo.</p><p>● <strong>CornerNet</strong>:<br> Goes for the corners of objects instead of the center.<br> Example: Detecting the four edges of a car to determine its bounding box.</p><h3>Object Detection vs. Other Computer Vision Techniques</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/936/1*go1IXau8-67ab90Ih-V6KQ.png" /></figure><h3>Challenges in Object Detection</h3><p>Problems like real-time processing and <a href="https://www.atltranslate.com/ai/blog/7-problems-in-object-detection-you-cant-ignore#:~:text=Deformation%20and%20aspect%20ratio%20variation,algorithms%20to%20accurately%20identify%20them.">finding uncommon things continue to be at the forefront of object detection</a> research despite breakthroughs.</p><p>Let’s investigate the main issues.</p><h4>1. Scale Variability: Big vs. Small</h4><p>● <em>Problem?</em><strong><br> </strong>Objects come in all sizes! — from tiny buttons to large cars. It’s tough for a model to detect both without getting confused.</p><p>● <em>It matters..?</em><strong><br> </strong>A model might miss small objects or struggle to locate bigger ones, leading to errors in detection accurately.</p><h4>2. Occlusion: When Objects Hide from View</h4><p>● Imagine a person standing behind a tree. We cannot see the whole person because some parts of their body are hidden behind the tree. <em>That’s called occlusion</em>. It’s a challenge for object detection.</p><p>● When objects are partially hidden, <em>detecting them correctly becomes difficult, impacting accuracy.</em></p><h4>3. Class Imbalance: More of One, Less of Another</h4><p>● Issue: Some objects appear much more often than others in datasets (like cars in a traffic camera feed), so the model is biased toward detecting those frequent objects.</p><p>● Why it matters?<strong> </strong>If the model is over-trained on one object, it might miss less frequent ones, leading to poor overall performance.</p><h4>4. Real-Time Performance: Speed vs. Accuracy</h4><p>● What is the problem?<strong><br> </strong>Images need to be processed quickly by object detection models. However, if high accuracy is aimed, they may be slowed down. Balancing the two is considered tricky.</p><p>● Why it matters?<strong><br> </strong>Speed is essential in applications such as self-driving vehicles. If the model is too slow, it might miss critical objects, like pedestrians.</p><h4>5. Generalization: Models Struggling with New Data</h4><p>● What is the problem?<strong><br> </strong>Object detection models are often trained on specific datasets. When faced with new environments or unseen objects, they might struggle.</p><p>● Why it matters?<strong><br> </strong>For real-world applications, models must adapt to new, unfamiliar situations without failing.</p><h4>6. Edge Computing Constraints: The Small Device Dilemma</h4><p>● Problem<strong>: </strong>Deploying object detection models on smaller devices like smartphones or IoT systems is tough. These devices don’t have the processing power of large servers.</p><p>● Why does it matter?<strong> </strong>The challenge is fitting a high-performing model onto a smaller device without compromising too much on accuracy or speed. It requires optimization techniques and creative solutions.</p><h3>Use Cases: Object Detection in Healthcare</h3><p>Object detection is revolutionizing healthcare by providing quicker and more accurate diagnoses. Here’s how it makes a difference:</p><h3>1. Tumor Detection</h3><p>A role of prime importance for identification purposes is being played by AI in tumors inside medical scans like X-rays and MRIs. <a href="https://health.google/caregivers/mammography/">Google’s Health AI system</a> has illustrated an unparalleled efficiency in noticing breast cancers more precisely compared to human radiologists, which leads to a well-timed detection and more effective treatment. The cancers show up as potentially dangerous because they might otherwise pass unidentified.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/932/1*au4w0qSp5wTFbhce_l3j0A.png" /><figcaption><strong>Source: </strong><a href="https://www.mdpi.com/2076-3417/13/16/9158">The workflow of the proposed method for brain cancer detection and localization</a></figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/897/1*zKdZ8b1HA0lspKFl49tmyA.png" /><figcaption><strong>Source: </strong><a href="https://www.mdpi.com/2076-3417/13/16/9158">Visual representation of brain tumor detection</a></figcaption></figure><h3>2. Disease Screening</h3><p>Object detection is changing <a href="https://dermnetnz.org/topics/what-is-dermatology">dermatology </a>as it can assist in cancer detection in the skin.</p><p>A deep learning model made by Stanford University, by training on thousands of pictures of the skin, accurately detects melanoma at par with dermatologists. It facilitates early identification of dangerous signs associated with skin lesions, leading to a higher survival possibility for patients.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/932/1*qPxl7dycbbjmx1YboEmj1Q.png" /><figcaption><strong>Source</strong>: Reprinted with permission from the Edinburgh Dermofit Library, featured in <a href="https://www.nature.com/articles/nature21056.epdf">“Dermatologist-level classification of skin cancer with deep neural networks”</a>.</figcaption></figure><p>Through these advancements, <a href="https://www.ijeast.com/papers/159-166,Tesma607,IJEAST.pdf">object detection is helping healthcare</a> professionals save lives and improve patient care. See more about <a href="https://viso.ai/deep-learning/object-detection/#elementor-toc__heading-anchor-16">object detection applications and use cases</a>.</p><h3>3. Using Healthcare AI to Identify Tissue Structure Changes Over Time</h3><p>Researchers can now more accurately identify changes in tissue structure over time__Thanks to computer vision.</p><p>So, it provides valuable insights into how illnesses develop and progress. Machine vision is particularly helpful for examining microscopic tissue.</p><p>In the analysis of individual cells, researchers have been able to study their composition and interactions with neighboring cells. This has opened up new possibilities for understanding cellular behavior in more detail.</p><p>Researchers can develop new treatments and therapies by observing how tissue structure changes as the disease progresses.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/932/1*9cS42MOvKOiVcfFCBZ8IDg.png" /><figcaption><strong>Source: </strong><a href="https://viso.ai/applications/computer-vision-in-healthcare/#elementor-toc__heading-anchor-15">Cell instance segmentation with Computer Vision</a></figcaption></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=146462f1d7c9" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Different types of Encoding Techniques in Machine Learning]]></title>
            <link>https://abdullahzunorain.medium.com/encoding-techniques-in-machine-learning-0d40bc76bb48?source=rss-2e9c562555a5------2</link>
            <guid isPermaLink="false">https://medium.com/p/0d40bc76bb48</guid>
            <dc:creator><![CDATA[Abdullah Zunorain]]></dc:creator>
            <pubDate>Sun, 16 Jun 2024 22:42:07 GMT</pubDate>
            <atom:updated>2024-06-16T22:44:38.707Z</atom:updated>
            <content:encoded><![CDATA[<p>In the ever-evolving world of machine learning, data is the cornerstone of every model’s success. However, raw data often comes in various forms that are not immediately suitable for algorithmic processing. This is where encoding steps in, transforming data into a format that machine learning models can understand and leverage effectively. In this article, we’ll explore the diverse types of encoding techniques and their applications in machine learning.</p><p>The types of encoding in machine learning can be broadly categorized into two main groups: <em>categorical data encoding</em> and <em>numerical data encoding</em>. Here are the common types of encoding used in machine learning:</p><h3><strong>1. Categorical Data Encoding</strong></h3><p>Categorical data encoding is used to convert categorical (qualitative) data into numerical values so that machine learning algorithms can process them.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/759/1*AaqTUM8Ucmr6DIoKcnGaAg.png" /><figcaption><strong>Categorical Col’s</strong></figcaption></figure><p>Categorical data represents qualitative attributes, such as names, labels, or other identifiers that need to be converted into numerical values. Here are some popular categorical encoding methods:</p><h4>Label Encoding:</h4><ul><li>Converts each unique category value to an integer.</li><li>Example: [&#39;cat&#39;, &#39;dog&#39;, &#39;mouse&#39;] becomes [0, 1, 2].</li><li>Suitable for ordinal data where there is a meaningful order.</li></ul><pre>from sklearn.preprocessing import LabelEncoder<br><br>data = categorical_cols[&quot;Gender&quot;]<br>label_encoder = LabelEncoder()<br>encoded_data = label_encoder.fit_transform(data)<br>print(&quot;Encoded Data:&quot;, encoded_data)<br>print(&quot;Length of Encoded Data:&quot;, len(encoded_data))</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/397/1*sX_Lkv1b2aHUNrcYhc9TyA.png" /><figcaption><strong>label encoding</strong></figcaption></figure><h4><strong>One-Hot Encoding:</strong></h4><ul><li>Converts each category value into a new binary column.</li><li>Example: [&#39;cat&#39;, &#39;dog&#39;, &#39;mouse&#39;] becomes [[1, 0, 0], [0, 1, 0], [0, 0, 1]].</li><li>Suitable for nominal data where there is no ordinal relationship.</li></ul><pre>import pandas as pd<br><br># Sample data<br>data = categorical_cols<br>df = pd.DataFrame(data, columns=[&#39;CALC&#39;])<br>one_hot_encoded = pd.get_dummies(df[&#39;CALC&#39;])<br><br>one_hot_encoded</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/496/1*pi4ovka56P-y7f2PRGtv_g.png" /><figcaption><strong>One Hot Encoding</strong></figcaption></figure><h4><strong>Binary Encoding:</strong></h4><ul><li>Converts categories into binary numbers and splits these binary digits into separate columns.</li><li>Example: [&#39;A&#39;, &#39;B&#39;, &#39;C&#39;] might become [&#39;001&#39;, &#39;010&#39;, &#39;011&#39;], which is then split into separate columns.</li><li>Useful when dealing with high cardinality categorical data.</li></ul><pre>import category_encoders as ce<br><br># Sample data<br>data = categorical_cols<br>df = pd.DataFrame(data, columns=[&#39;CAEC&#39;])<br>binary_encoder = ce.BinaryEncoder(cols=[&#39;CAEC&#39;])<br>binary_encoded = binary_encoder.fit_transform(df)<br><br>binary_encoded</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/400/1*mRs4RbY58f2fAnQ7yhVTyg.png" /><figcaption><strong>Binary Encoding</strong></figcaption></figure><h4><strong>Target Encoding (Mean Encoding):</strong></h4><ul><li>Replaces each category with the mean of the target variable for that category.</li><li>Example: For a target variable, categories like [&#39;A&#39;, &#39;B&#39;, &#39;C&#39;] might be encoded based on their mean target values [0.5, 0.3, 0.8].</li></ul><pre>import pandas as pd<br>import category_encoders as ce<br><br># Sample data<br>data = pd.DataFrame(<br>    {<br>        &#39;category&#39;: [&#39;A&#39;, &#39;B&#39;, &#39;C&#39;, &#39;A&#39;, &#39;B&#39;, &#39;C&#39;], <br>        &#39;target&#39;: [1, 2, 3, 4, 5, 6]<br>    }<br> )<br>target_encoder = ce.TargetEncoder(cols=[&#39;category&#39;])<br>target_encoded = target_encoder.fit_transform(data[&#39;category&#39;], data[&#39;target&#39;])<br><br>target_encoded</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/248/1*sHA2idjPIpqMvqjLD03yyQ.png" /><figcaption><strong>Target Encoding</strong></figcaption></figure><h4><strong>Frequency Encoding:</strong></h4><ul><li>Replaces each category with its frequency or count.</li><li>Example: [&#39;cat&#39;, &#39;cat&#39;, &#39;dog&#39;] might become [2, 2, 1].</li></ul><pre>import pandas as pd<br><br># Sample data<br>data = categorical_cols[&quot;MTRANS&quot;]<br>frequency_encoded = data.map(data.value_counts())<br><br>pd.DataFrame(frequency_encoded)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/317/1*-gHeaivhkq0ic32TzAn6JQ.png" /><figcaption><strong>Frequency Encoding</strong></figcaption></figure><h4><strong>Ordinal Encoding:</strong></h4><ul><li>Assigns an integer value to each category, preserving the order.</li><li>Example: [&#39;low&#39;, &#39;medium&#39;, &#39;high&#39;] becomes [1, 2, 3].</li></ul><pre>from sklearn.preprocessing import OrdinalEncoder<br><br># Sample data<br>data = [[&#39;low&#39;], [&#39;medium&#39;], [&#39;high&#39;]]<br>ordinal_encoder = OrdinalEncoder(categories=[[&#39;low&#39;, &#39;medium&#39;, &#39;high&#39;]])<br>ordinal_encoded = ordinal_encoder.fit_transform(data)<br><br>pd.DataFrame(ordinal_encoded)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/253/1*Zyb4GZXSJv4ZL6Q_Skp29w.png" /><figcaption><strong>Ordinal Encoding</strong></figcaption></figure><h3>2. Numerical Data Encoding</h3><p>Numerical data encoding involves transforming numerical features to make them more suitable for certain machine learning algorithms.</p><h4><strong>Normalization (Min-Max Scaling):</strong></h4><ul><li>Scales the data to a fixed range, usually [0, 1].</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/705/1*dOMqLcH_loUQAmGB_pxg5g.png" /></figure><h4><strong>Standardization (Z-Score Normalization):</strong></h4><ul><li>Scales the data to have a mean of 0 and a standard deviation of 1.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*f6YDLICON3mUABCCVj8gIA.png" /></figure><h4><strong>Binning (Discretization):</strong></h4><ul><li>Divides continuous values into discrete bins or intervals.</li><li>Example: Age can be binned into categories like [&#39;0-10&#39;, &#39;11-20&#39;, &#39;21-30&#39;].</li></ul><h4><strong>Log Transformation:</strong></h4><ul><li>Applies the logarithm to data, often used to reduce skewness.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/554/1*BNyjGj0BpWJNe4aQA83_fw.png" /></figure><h4><strong>Polynomial Features:</strong></h4><ul><li>Generates new features by taking powers of existing features.</li><li>Example: For a feature x, polynomial features could include x^2, x^3, etc.</li></ul><h4><strong>Power Transformation:</strong></h4><ul><li>Stabilizes variance and makes the data more Gaussian-like.</li><li>Includes techniques like <em>Box-Cox</em> and <em>Yeo-Johnson</em> transformations.</li></ul><h3>Conclusion</h3><p>Understanding and choosing the right encoding technique is crucial for the success of any machine learning project. Each encoding method has its strengths and is suited to specific types of data and machine learning tasks. By leveraging the appropriate encoding strategies, you can ensure that your data is in the best possible format for model training, leading to more accurate and robust predictions. Happy encoding!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=0d40bc76bb48" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Codex Market Analysis: Exploring Consumer Trends in Indian Using Excel]]></title>
            <link>https://abdullahzunorain.medium.com/codex-market-analysis-exploring-consumer-trends-in-indian-using-excel-8e5eaedf860a?source=rss-2e9c562555a5------2</link>
            <guid isPermaLink="false">https://medium.com/p/8e5eaedf860a</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[excel]]></category>
            <category><![CDATA[marketing-strategies]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-visualization]]></category>
            <dc:creator><![CDATA[Abdullah Zunorain]]></dc:creator>
            <pubDate>Thu, 18 Apr 2024 07:21:12 GMT</pubDate>
            <atom:updated>2024-04-20T18:06:51.076Z</atom:updated>
            <content:encoded><![CDATA[<p>Understanding consumer preferences and behaviors is essential for any company aiming to succeed in today’s competitive market landscape. As consumer demands evolve and competition intensifies, businesses must uses data-driven insights to tailor their products, marketing strategies, and brand positioning effectively. In this article, we delve into the journey of CodeX, a German beverage company venturing into the Indian market, and explore how data analysis has illuminated key consumer trends, guiding CodeX’s strategic decisions for growth and success.</p><h3>Introduction</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/383/1*_ky9TjMpS412nNluLwR3RA.png" /></figure><p>CodeX is a German beverage company aiming to make its mark in the Indian market. A few months ago, they launched their energy drink in 10 cities in India. Their marketing team is responsible for increasing brand awareness, market share, and product development.</p><p>They conducted a survey in 10 cities to get data for energy drinks.<br>As a data analyst, I transformed these survey results into meaningful insights. The purpose of this project is to analyze the survey data collected by CodeX in 10 cities across India regarding their energy drink launch. By dissecting this data, the aim is to uncover valuable insights into consumer preferences, behaviors, and trends related to energy drinks. These insights will serve as a guiding light for CodeX’s marketing team, enabling them to devise targeted strategies for enhancing brand awareness, capturing a larger market share, and refining product offerings to better align with the preferences of Indian consumers.</p><p>Ultimately, the goal is to empower CodeX with actionable insights that will drive informed decision-making and propel their success in the competitive Indian beverage market.</p><p>Domain: F &amp; B Function: Marketing</p><h3>Transforming Data into Actionable Intelligence</h3><p>In our exploration of the Indian beverage market, data analysis played a vital role in decoding consumer preferences and behaviors. Through precise analysis techniques and using tools like Excel, we converted raw survey data from 10 cities into actionable insights. Despite challenges like data cleansing and data manipulation, innovative solutions ensured the reliability of our findings. This process underscores the crucial role of data analysis in informing CodeX’s strategic decisions as they navigate the competitive landscape of the Indian market.</p><h3>Primary Insights: Understanding Indian Consumer Preferences</h3><p><strong>1.</strong> <strong>DEMOGRAPHIC INSIGHTS</strong></p><p><em>a. Who prefers energy drink more ?</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1013/1*dEYZUIgLOwvLMF13xnSFCg.png" /></figure><p>Males demonstrate a higher preference for energy drinks compared to females and non-binary individuals.</p><p><em>b. Which age group prefers energy drinks more?</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/977/1*G1rZU6MLD1rjfWGIzbV7hg.png" /></figure><p>The age group of 19–30 demonstrates the highest preference for energy drinks. The company should focus its marketing efforts on the 19–30 age group, as they demonstrate the highest preference for energy drinks among other age groups, with approximately 5.5K individuals. This strategic targeting can significantly enhance sales performance.</p><p><em>c. Which type of marketing reaches the most Youth (15–30)?</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/977/1*bQuuuCA2-iyMyNBK-4qi1Q.png" /></figure><p>Online ads reach the most youth (15–30 age group), with 2666 respondents exposed to them, followed by TV commercials with 1290 respondents.</p><p><strong>2.</strong> <strong>CONSUMER PREFERENCES</strong></p><p><em>a. What are the preferred ingredients of energy drinks among respondents?</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/973/1*qUdIPcUL5BpxkXPb9j8oLg.png" /></figure><p>The preferred ingredients of energy drinks among respondents, according to the data, are caffeine, vitamins, sugar, and guarana, but the most preferred ingredient is caffeine.</p><p><em>b. What packaging preferences do respondents have for energy drinks?</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1009/1*r4-EnBaYDPMlkWOAArSSYA.png" /></figure><p>As per the analysis, respondents favor energy drink packaging that offers compact and portable cans, with a total count of 3984.</p><p><strong>3.</strong> <strong>COMPETITION ANALYSIS</strong></p><p><em>a. Who are the current market leaders?</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/997/1*BBwuGf_WrTKYNjG7D2ogPQ.png" /></figure><p>In the competitive landscape of the Indian beverage market, Cola-Coka, Bepsi, and Gangster emerge as frontrunners, with Cola-Coka taking the lead. With 2.5k consumers, Cola-Coka commands the highest consumption rate, closely followed by Bepsi with 2.1k consumers and Gangster with 1.9k consumers.</p><p><em>b. What are the primary reasons consumers prefer those brands over ours?</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*puB3uGjCiVQuYjah1aB4bQ.png" /></figure><p>The primary reasons consumers prefer other brands over CodeX are:</p><p>- Brand reputation</p><p>- Taste/flavor preference</p><p><strong>4. MARKETING CHANNELS AND BRAND AWARENESS</strong></p><p><em>a. Which marketing channel can be used to reach more customers?</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1021/1*QlrwgPuQbKZslR6MVHS-JQ.png" /></figure><p>The marketing channels that can reach the most customers are online ads and TV commercials, with 4020 and 2688 respondents exposed to them, respectively.</p><p><em>b. How effective are different marketing strategies and channels in reaching our customers?</em></p><p>Online ads and TV commercials stand out as highly effective channels, reaching 4020 and 2688 respondents, respectively. In comparison, outdoor billboards, other channels, and print media have lower but still significant reach, engaging 1226, 1225, and 841 respondents, respectively.</p><p><strong>5.</strong> <strong>BRAND PENETRATION</strong></p><p><em>a. What do people think about our brand? (Overall rating)</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1019/1*ejDZTxZZVR9FlT_P4FrslQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*I4p1yi7p0G8Y7doXobf_6w.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1009/1*9z_tCzktaao09lq4uLQYuQ.png" /></figure><p>CodeX demonstrates a competitive 3.3 rating, aligning with industry standards. Enhancing taste profiles and expanding product availability channels are imperative to drive consumer satisfaction and maintain market relevance.</p><p><em>b. Which cities do we need to focus more on?</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0G_RgtKS54jzhlId2LNbMw.png" /></figure><p>Cities such as Bangalore, Hyderabad, and Mumbai exhibit a higher proportion of neutral perceptions, indicating areas where focused efforts are needed to shift consumer sentiment towards positivity and enhance brand perception.</p><p><strong>6.</strong> <strong>PURCHASE BEHAVIOR</strong></p><p><em>a. Where do respondents prefer to purchase energy drinks?</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/987/1*bMOsyqI06LZoBOH-vqrOZA.png" /></figure><p>Respondents prefer purchasing energy drinks from supermarkets, with 4494 indicating this as their preferred purchase location.</p><p><em>b. What are the typical consumption situations for energy drinks among respondents?</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/997/1*NY1ZSM10E8p77vM9YEKabQ.png" /></figure><p>People consume energy drinks during work, study, or exercise, often preceding physical activity for an energy boost.</p><p><em>c. What factors influence respondents’ purchase decisions, such as price range and limited edition packaging?</em></p><ul><li><em>Price Range:</em></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1019/1*rzXqusrlCXhsT41Wio90rg.png" /></figure><ul><li><em>Limited Edition Packaging:</em></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1006/1*H4Ea2Zu3d26_rEWa7SIq4Q.png" /></figure><p>Price range significantly influences purchase decisions, with a majority falling within the 50–99 and 100–150 ranges.</p><p>Limited edition packaging is also influential, with a notable preference among respondents.</p><p><strong>7. PRODUCT DEVELOPMENT</strong></p><p><em>a. Which area of business should we focus more on our product development? (Branding/taste/availability)</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1006/1*7i5v3sQEclVofjPBtgBP3A.png" /></figure><p>Based on the data, focusing on enhancing brand reputation and availability could be a priority for product development, as these both has the highest count among the factors influencing consumer preferences for CodeX.</p><h3>Secondary Insights: Navigating Market Dynamics</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1009/1*9z_tCzktaao09lq4uLQYuQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1009/1*PfvfSFgfBwqZ7NkRywyqxA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/967/1*o53a2VcYNyYBRjYuPrUCfQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/967/1*PasEDrNu3_3SU6ZHn17POg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/991/1*kBzSS7oIkktA1PWaqfrJDA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/983/1*XaL0fWatRNxUm944DghuTQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1014/1*NKn_e2y1T2CnGxp8Fpnt3g.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1023/1*8XucfP44-tmfVJYJRMZSOg.png" /></figure><h3>Recomendations</h3><p><strong><em>1. Price Range Alignment:</em></strong></p><ul><li>Based on analysis, a significant portion of CodeX users falls within the price range of 50–99.</li><li>Consider adjusting pricing strategies to cater to this segment’s preferences and affordability.</li></ul><p><strong><em>2. Desired Product Enhancements:</em></strong></p><p>Respondents express a desire for:</p><ul><li>Reduced sugar content</li><li>More natural ingredients</li></ul><p>Implementing these improvements could address health concerns and align with consumer preferences for healthier options.</p><p><strong><em>3. Reasons for Brand Loyalty:</em></strong></p><p>CodeX brand loyalty is driven by:</p><ul><li>Brand reputation</li><li>Availability</li></ul><p>Emphasize these strengths in marketing efforts to reinforce brand loyalty and attract new customers.</p><p><strong><em>4. Health Concerns and Product Safety:</em></strong></p><p>A significant number of respondents (597) express health concerns related to the CodeX product.</p><p>Consider initiatives to address these concerns, such as transparent labeling, highlighting product safety measures, and promoting health-conscious formulations.</p><p><strong><em>5. Packaging Preferences:</em></strong></p><p>Exploring limited edition packaging options based on consumer preferences, such as those expressed by 415 respondents, could enhance product appeal and attract new consumers. This initiative aligns with the analysis indicating a significant interest in limited edition packaging among CodeX users.</p><p><strong><em>6. Expected Ingredients:</em></strong></p><ul><li>Respondents expect CodeX to contain caffeine and vitamins.</li></ul><p>Ensure product formulations align with these expectations and consider incorporating additional health-conscious ingredients to enhance perceived value and appeal.</p><p><strong><em>7.</em></strong><em> </em><strong><em>Marketing Campaigns for CodeX Product:</em></strong></p><p>Based on the analysis, we can focus on running digital marketing campaigns, particularly online ads, to reach a larger audience of CodeX consumers. Additionally, investing in TV commercials can complement online efforts and further enhance brand visibility.</p><p><strong><em>8.</em></strong><em> </em><strong><em>Brand Ambassador:</em></strong></p><p>Brand ambassadors such as Virat Kohli, Sachin Tendulkar, Dhoni, and other celebrities with connections to exercise and sports can enhance product visibility and promote the product effectively. Additionally, tennis stars and other athletes can also serve as impactful ambassadors due to their influence and relevance to the target audience.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/908/1*-ZVCamKsuHbsYZxsSGNuXA.jpeg" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/696/1*eL1sf0f3BQHTPVkw97TGgQ.jpeg" /></figure><p><strong><em>9.</em></strong><em> </em><strong><em>Target Audience:</em></strong></p><p>The targeted audience for CodeX should be the youth (15–30) due to their significant representation, aligning with the brand’s energy drink positioning aimed at active lifestyles and vitality.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/977/1*G1rZU6MLD1rjfWGIzbV7hg.png" /></figure><h3>Conclusion:</h3><p>In conclusion, the comprehensive analysis of the survey data provides invaluable insights into the Indian beverage market, specifically regarding energy drinks. Through accurate data analysis and interpretation, we’ve gained a deeper understanding of consumer preferences, behaviors, and market dynamics. The findings highlight key areas for CodeX’s strategic focus, including product development, marketing strategies, and brand positioning. By aligning product offerings with consumer expectations, optimizing marketing channels, and addressing health-conscious preferences, CodeX can enhance its market presence and competitiveness in India.</p><p>Moreover, exploring insights on consumer demographics and purchase behavior enables CodeX to tailor its strategies to target the most favorable market segments effectively. As a data analyst, my role extends beyond data interpretation to actionable recommendations that empower CodeX to make informed decisions and drive sustainable growth in the dynamic landscape of the Indian beverage market.</p><p><strong><em>For a more detailed overview of the project and access to the analysis, please visit the GitHub repository: [</em></strong><a href="https://github.com/abdullahzunorain/CodeX_Market_Analysis_Using_Excel"><strong><em>project repo</em></strong></a><strong><em>]. Thank you for joining me on this journey through the dynamic world of consumer insights.</em></strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8e5eaedf860a" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQL Case Study Project: Food-Fi (Challenge)]]></title>
            <link>https://abdullahzunorain.medium.com/sql-case-study-project-food-fi-challenge-2a4a89317497?source=rss-2e9c562555a5------2</link>
            <guid isPermaLink="false">https://medium.com/p/2a4a89317497</guid>
            <dc:creator><![CDATA[Abdullah Zunorain]]></dc:creator>
            <pubDate>Sat, 06 Apr 2024 03:25:34 GMT</pubDate>
            <atom:updated>2024-04-18T05:59:50.028Z</atom:updated>
            <content:encoded><![CDATA[<h3>SQL Case Study Project: Foodie-Fi</h3><p><strong><em>Analyzing Data for Food Fi Company Using SQL</em></strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*j8Pa1XTouYsdD7NKRmxXdA.jpeg" /><figcaption>Image sourced from Google for illustrative purposes only.</figcaption></figure><p>Welcome to the SQL Case Study Project on Food-Fi! In this project, we explore into the world of data-driven decision-making within the context of a fictional streaming service dedicated to culinary content. With SQL, we analyze subscription data, uncover customer preferences, and tackle business challenges head-on. Join us as we explore how data insights drive success in the competitive landscape of streaming media.</p><blockquote><strong><em>Introduction:</em></strong></blockquote><p>In the bustling landscape of subscription-based businesses, Danny recognized a significant gap in the market: a lack of dedicated platforms offering exclusive cooking content. Driven by his passion for food and inspired by the success of streaming giants like Netflix, Danny envisioned a platform that catered specifically to food enthusiasts and culinary aficionados. Thus, Foodie-Fi was born.</p><p>Danny finds a few smart friends to launch his new startup Foodie-Fi in 2020 and started selling monthly and annual subscriptions, giving their customers unlimited on-demand access to exclusive food videos from around the world!</p><p>Central to Foodie-Fi’s mission is its unfluctuating commitment to data-driven decision-making. Recognizing the transformative power of data analytics, Danny ensured that every investment and strategic decision for the platform was informed by robust data insights. By utilizing the wealth of subscription-style digital data at their disposal, the Foodie-Fi team tried to optimize customer satisfaction, drive retention, and propel the platform to success in the fiercely competitive streaming media industry.</p><p>In this case study, we look into the complexities of Foodie-Fi’s data-driven approach, exploring how subscription-style digital data is influenced to answer critical business questions and shape the platform’s trajectory. From understanding customer preferences to optimizing pricing strategies and enhancing content offerings, join us on a journey through the world of Foodie-Fi and discover the transformative potential of data in shaping the future of streaming media.</p><blockquote><strong><em>Available Dataset:</em></strong></blockquote><h4><em>Table 1: Plans</em></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/351/1*pQCVff-Q-mRLXeRIUo5bjA.png" /><figcaption>plans table</figcaption></figure><p>The “plans” table within the Foodie-Fi dataset outlines the subscription options available to customers. It features a variety of plans catering to different preferences and needs. Customers can choose from the basic monthly plan, offering limited access for a fixed monthly fee of $9.90, or opt for the pro monthly plan, providing unlimited access and additional features for $19.90 per month. Alternatively, customers can commit to the pro annual plan, which offers the same benefits as the pro monthly plan but at a discounted rate of $199 for a year-long subscription with some other benefits too.</p><p>Additionally, customers are offered a 7-days free trial of the pro monthly plan, allowing them to experience the platform before making a commitment. For customers who decide to cancel their subscription, a churn plan record is created with a null price, ensuring continued access until the end of the billing period.</p><h4>Table 2: Subscriptions</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/357/1*ZbriXK6bheAPCrMRvR-S_g.png" /><figcaption>Subscriptions table</figcaption></figure><p>The “subscriptions” table tracks customer subscriptions and their associated start dates within the Foodie-Fi platform. Each entry in this table represents a customer’s chosen subscription plan and the date it begins. In cases where customers upgrade or downgrade their plans, the start_date reflects the date of the change, with the higher plan taking effect immediately.</p><p>Furthermore, churned customers are identified in this table, maintaining access until the end of their current billing period despite their decision to cancel. This table provides crucial insights into customer subscription behaviors, including trends in plan upgrades, downgrades, and churn rates. By analyzing subscription data from this table, Foodie-Fi can make informed decisions to optimize pricing strategies, enhance customer retention efforts, and improve overall business performance.</p><p><strong>To learn more and get all the details about this case study visit this </strong><a href="https://8weeksqlchallenge.com/case-study-3/"><strong>link</strong></a><strong>.</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8gwys6ZFyJe6g4xxPXz6qg.png" /><figcaption><strong>Entity Relationship Diagram</strong></figcaption></figure><p>You can check this <a href="https://github.com/abdullahzunorain/SQL-case-study_Food-Fi/blob/main/Foodie-Fi%20Schema.sql"><em>link</em></a><em> </em>to create these tables.</p><blockquote><strong><em>Business Problems and Objectives:</em></strong></blockquote><h4><strong>Business Problems:</strong></h4><ul><li><strong>Subscription Retention: </strong>Food Fi faces challenges in retaining subscribers and reducing churn rates.</li><li><strong>Understanding Customer Preferences: </strong>Lack of insights into customer preferences regarding subscription plans and content preferences.</li><li><strong>Optimizing Pricing Strategy: </strong>Need to determine the most effective pricing strategy to attract and retain customers while maximizing revenue.</li></ul><h4><strong>Objectives of Using SQL for Data Analysis:</strong></h4><ul><li><strong>Identifying Patterns: </strong>Utilize SQL to analyze subscription data and identify patterns in customer behavior, such as subscription trends and viewing habits.</li><li><strong>Optimizing Plans:</strong> Use SQL to analyze subscription data and tailor subscription plans to match customer preferences, thereby improving retention rates.</li><li><strong>Price Optimization: </strong>Employ SQL to analyze pricing data and determine optimal price points for subscription plans, balancing revenue generation with customer satisfaction and retention.</li></ul><blockquote><strong><em>Data Analysis Questions:</em></strong></blockquote><ol><li><strong>How many customers has Foodie-Fi ever had?</strong></li></ol><pre>SELECT <br> COUNT(DISTINCT customer_id) AS total_customers<br>FROM subscriptions;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/228/1*SmyL2vPzEatwvtZV48JsTg.png" /></figure><p>The first query calculates the total number of unique customers that Foodie-Fi has ever had by counting the distinct customer IDs from the subscriptions table.</p><p>In this case, the result is 1000, indicating that Foodie-Fi has had 1000 unique customers over the specified time period.</p><p><strong>2. What is the monthly distribution of trial plan start_date values for our dataset use the start of the month as the group by value?</strong></p><pre>-- method_01<br>select count(plan_id) as counts_of_plans, month(start_date) as months<br>from subscriptions<br>group by months, plan_id<br>having plan_id=0;<br><br>-- method_02<br>select count(plan_id), month(start_date) as months<br>from subscriptions<br>where plan_id=0<br>group by months, plan_id;<br><br>-- method_03<br>SELECT COUNT(plan_id), MONTH(start_date) AS months<br>FROM subscriptions<br>WHERE plan_id = 0<br>GROUP BY months;<br><br>-- method_04 (this query will not work in MySQL)<br>SELECT <br>DATE_TRUNC(&#39;month&#39;,start_date) as month,<br>COUNT(customer_id) as trial_starts<br>FROM subscriptions<br>WHERE plan_id = 0<br>GROUP BY DATE_TRUNC(&#39;month&#39;,start_date);</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/296/1*kavrIr1NbylPoPjg8YE-_Q.png" /></figure><p>The above query calculates the monthly distribution of trial plan start_date values for the dataset. It groups the data by the start month of each trial plan and counts the occurrences of trial plan start dates within each month. The `plan_id` column is filtered to only include trial plans (where `plan_id` equals 0).</p><p>Finally, the results are displayed with the count of trial plans and the corresponding month of their start_date values.</p><p><strong>3. What plan start_date values occur after the year 2020 for our dataset? Show the breakdown by count of events for each plan_name?</strong></p><pre>select <br> s.plan_id,<br>    p.plan_name,<br>    count(p.plan_name) as event_count<br>from subscriptions s inner join plans p<br>on s.plan_id = p.plan_id<br>where year(s.start_date) &gt; 2020<br>group by s.plan_id, p.plan_name;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/382/1*0SdckZIjnsPKJWnVPF4eJA.png" /></figure><p>The above query groups the data by plan_id and plan_name, counting the occurrences of each plan_name.</p><p>Finally, it presents the breakdown of events by plan_name along with the corresponding event_count.</p><p><strong>4. What is the customer count and percentage of customers who have churned rounded to 1 decimal place?</strong></p><pre>select <br> count(distinct customer_id) as customer_count, <br>    round((count(distinct customer_id)/(SELECT COUNT(DISTINCT customer_id) FROM subscriptions)) * 100, 1) as percentage<br>from subscriptions <br>where plan_id=4;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/318/1*sPZw1ODc2OfUP8F7hXqcZg.png" /></figure><p>This query calculates the customer count and percentage of customers who have churned, rounded to 1 decimal place. It first counts the number of distinct customer IDs where the plan_id is equal to 4 (indicating churn). Then, it divides this count by the total count of distinct customer IDs in the subscriptions table, multiplying by 100 to get the percentage.</p><p>The result shows that 307 customers have churned, which is approximately 30.7% of the total customer base.</p><p><strong>5. How many customers have churned straight after their initial free trial what percentage is this rounded to the nearest whole number?</strong></p><pre>-- Method_01<br>SELECT<br>    COUNT(prev_plan) AS cnt_churn,<br>    ROUND(COUNT(*) * 100 / (SELECT COUNT(DISTINCT customer_id) FROM subscriptions), 0) AS perc_churn<br>FROM<br>    (SELECT<br>         *,<br>         LAG(plan_id, 1) OVER(PARTITION BY customer_id ORDER BY plan_id) AS prev_plan<br>     FROM<br>         subscriptions) AS cte_churn<br>WHERE<br>    plan_id = 4 AND prev_plan = 0;<br><br><br><br>-- Method_02 (using CTE)<br>/*<br> CTE Explanation:<br>    <br>    Certainly! Let&#39;s break down the query and explain each part in detail:<br><br><br> 1. SELECT *:<br>    - This selects all columns from the `subscriptions` table.<br><br> 2. LAG(plan_id, 1):<br>    - The LAG function looks at the value of a column from the previous row in the result set. In this case, it looks at the `plan_id` column.<br>    - The second parameter, `1`, specifies that it should look back one row.<br><br> 3. OVER (PARTITION BY customer_id ORDER BY plan_id):<br>    - The OVER clause defines how the LAG function should be applied.<br>    - `PARTITION BY customer_id` means that the rows will be partitioned (or grouped) based on the `customer_id` column. So, the LAG function will only look at the previous row within the same `customer_id` group.<br><br> 4. AS prev_plan:<br>    - This assigns a name to the result of the LAG function. It&#39;s named `prev_plan`, indicating that it represents the previous plan that each customer had.<br><br> So, in simpler terms, this query creates a new column called `prev_plan`, which shows the previous plan that each customer had before their current plan. It uses the LAG function to look at the `plan_id` from the previous row within the same customer group, partitioned by `customer_id`, and ordered by `plan_id`. This helps us to analyze the transition between plans for each customer in the `subscriptions` table.<br>*/<br><br>WITH cte_churn AS (<br> SELECT *, LAG(plan_id, 1)  OVER(PARTITION BY customer_id) AS prev_plan<br> FROM subscriptions<br>)<br>SELECT<br> COUNT(prev_plan) AS cnt_churn,<br> ROUND(COUNT(*) * 100/(SELECT COUNT(DISTINCT customer_id) FROM subscriptions),0) AS perc_churn<br>FROM cte_churn<br>WHERE plan_id = 4 and prev_plan = 0;<br><br><br>-- Method_03 (using CTE)<br>WITH CTE_chrun AS (<br>SELECT customer_id, plan_name,<br>ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY start_date ASC) as row_numbr<br>FROM subscriptions as S<br>INNER JOIN plans as P on S.plan_id = P.plan_id<br>)<br>SELECT <br>COUNT(DISTINCT customer_id) as churned_after_trial,<br>ROUND((COUNT(DISTINCT customer_id) / (SELECT COUNT(DISTINCT customer_id) FROM subscriptions))*100,0) as percent_churn_after_trial<br>FROM CTE_chrun<br>WHERE row_numbr = 2<br>AND plan_name = &#39;churn&#39;;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/268/1*QX90K9ZbN8c7T-KeBT8ZjA.png" /></figure><p>The SQL code identifies customers who churned immediately after their initial free trial.</p><p>It calculates the count and percentage of such customers, which are 92 and approximately 9%, respectively.</p><p><strong>6. What is the number and percentage of customer plans after their initial free trial?</strong></p><pre>-- Method_01<br>/*<br>1. Common Table Expression (CTE) - explanation:<br>    - This part of the query defines a CTE named `cte_next_plan`.<br>    - The CTE selects all columns from the `subscriptions` table and calculates the next plan for each customer using the `LEAD` function.<br>    - `LEAD(plan_id, 1) OVER(PARTITION BY customer_id ORDER BY plan_id)` retrieves the next `plan_id` for each customer, partitioned by `customer_id` and ordered by `plan_id`.<br><br>2. Main Query - explanation:<br>    - This part of the query retrieves the result from the CTE and performs further calculations.<br>    - It selects the `next_plan`, counts the number of customers (`num_cust`), and calculates the percentage of customers (`perc_next_plan`) for each next plan.<br>    - The `WHERE` clause filters out rows where the `next_plan` is not null (meaning there is a next plan) and the current plan is a trial plan (plan_id = 0).<br>    - The `GROUP BY` clause groups the results by the next plan.<br>    - The `ORDER BY` clause orders the results by the next plan.<br><br> -&gt; In summary, this query analyzes the plans chosen by customers after their initial free trial. It calculates both the number and percentage of customers for each subsequent plan, excluding the trial plan itself.<br>*/<br><br>WITH cte_next_plan AS (<br> SELECT<br>  *,<br>  LEAD(plan_id, 1) OVER(PARTITION BY customer_id ORDER BY plan_id) AS next_plan<br> FROM subscriptions)<br>SELECT<br> next_plan,<br> COUNT(*) AS num_cust,<br> ROUND(COUNT(*) * 100/(SELECT COUNT(DISTINCT customer_id) FROM subscriptions),1) AS perc_next_plan<br>FROM cte_next_plan<br>WHERE next_plan is not null and plan_id = 0<br>GROUP BY next_plan<br>ORDER BY next_plan;<br><br><br>-- Method_02<br>WITH CTE AS (<br>SELECT customer_id, plan_name, ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY start_date ASC) as row_nmbr<br>FROM subscriptions S INNER JOIN plans P <br>ON P.plan_id = S.plan_id<br>)<br>SELECT <br>plan_name,<br>COUNT(customer_id) as customer_count,<br>ROUND((COUNT(customer_id) / (SELECT COUNT(DISTINCT customer_id) FROM CTE))*100,1) as customer_percent<br>FROM CTE<br>WHERE row_nmbr = 2<br>GROUP BY plan_name;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/486/1*NELQZgYdvnGT6-rHWdVpbw.png" /></figure><p>The SQL query analyzes customer behavior post their free trial on Foodie-Fi. It calculates the number and percentage of customers choosing different subscription plans after the trial.</p><p>The results show the count and percentage of customers opting for each subsequent plan, categorized by plan_id.</p><p><strong>7. What is the customer count and percentage breakdown of all 5 plan_name values at 2020–12–31?</strong></p><pre> -- method_01<br>/*<br> 1.Common Table Expression (CTE) - explanation:<br>  - WITH CTE AS: This defines a Common Table Expression (CTE) named `CTE`.<br>  - SELECT *: Selects all columns from the `subscriptions` table.<br>  - ROW_NUMBER() OVER(...): Assigns a row number to each row within each partition of `customer_id`, ordered by `start_date` in descending order. <br>        - * -&gt; This helps in identifying the latest subscription for each customer.<br>  - PARTITION BY customer_id: Partitions the data into groups based on the unique `customer_id`.<br>  - ORDER BY start_date DESC: Orders the rows within each partition by `start_date` in descending order, ensuring that the latest subscription comes first.<br>  - WHERE start_date &lt;= &#39;2020-12-31&#39;: Filters the data to include only subscriptions with a `start_date` before or on December 31, 2020.<br> 2.Main Query - explanation:<br>  - SELECT plan_name: Selects the plan name.<br>  - COUNT(customer_id) as customer_count: Counts the number of customers for each plan.<br>  - ROUND(...)*100,1) as percent_of_customers: Calculates the percentage of customers for each plan, rounded to one decimal place.<br>  - FROM CTE: Specifies that the data is retrieved from the Common Table Expression `CTE`.<br>  - INNER JOIN plans as P on CTE.plan_id = P.plan_id: Joins the CTE with the `plans` table on the `plan_id` column to retrieve the plan names.<br>  - WHERE rn = 1: Filters the data to include only the latest subscription for each customer (where `rn` is equal to 1).<br>  - GROUP BY plan_name: Groups the results by plan name.<br> In summary, this query calculates the number and percentage of customers for each plan based on their latest subscription before or on December 31, 2020. It uses a Common Table Expression to identify the latest subscription for each customer and then joins this data with the `plans` table to retrieve the plan names. Finally, it calculates the count and percentage of customers for each plan and groups the results by plan name.<br>*/<br>WITH My_CTE AS (<br>SELECT  *, <br>  ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY start_date DESC) as rwnmbr<br>FROM subscriptions<br>WHERE start_date &lt;= &#39;2020-12-31&#39;<br>)<br>SELECT <br> plan_name,<br> COUNT(customer_id) as customer_count,<br> ROUND((COUNT(customer_id)/(SELECT COUNT(DISTINCT customer_id) FROM My_CTE))*100,1) as percent_of_customers<br>FROM My_CTE mc INNER JOIN plans as P ON mc.plan_id = P.plan_id<br>WHERE rwnmbr = 1<br>GROUP BY plan_name;<br><br><br><br>-- method_02<br><br>WITH cte_next_date AS (<br> SELECT<br>  *,<br>  LEAD(start_date, 1) OVER(PARTITION BY customer_id ORDER BY start_date) AS next_date<br> FROM subscriptions<br>    WHERE start_date &lt;= &#39;2020-12-31&#39;),<br>plans_breakdown AS(<br> SELECT<br>  plan_id,<br>  COUNT(DISTINCT customer_id) AS num_customer<br> FROM cte_next_date<br> WHERE (next_date IS NOT NULL AND (start_date &lt; &#39;2020-12-31&#39; AND next_date &gt; &#39;2020-12-31&#39;))<br>    OR (next_date IS NULL AND start_date &lt; &#39;2020-12-31&#39;)<br> GROUP BY plan_id)<br>SELECT<br> plan_id,<br> num_customer,<br>    ROUND(num_customer * 100/(SELECT COUNT(DISTINCT customer_id) FROM subscriptions),1) AS perc_customer<br>FROM plans_breakdown<br>GROUP BY plan_id, num_customer<br>ORDER BY plan_id;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/514/1*MiVyuScj3M46z_hiXtTDvA.png" /></figure><p>This query calculates the count and percentage of customers for each plan based on their latest subscription. This provides valuable insights into the distribution of customer subscriptions across different plans at the end of 2020.</p><p><strong>8. How many customers have upgraded to an annual in 2020?</strong></p><pre>select count(*) as Upgraded_cust_count<br>from subscriptions<br>where Year(start_date) = 2020 and plan_id = 3;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/290/1*dW8RP705MTpvYveOmi3M5A.png" /></figure><p>The result, 195, signifies the total number of customers who opted for an annual subscription in 2020.</p><p><strong>9. How many days on average does it take for a customer to an annual plan from the day they join Foodie-Fi?</strong></p><pre>select * from subscriptions;<br>select * from plans;<br><br>with trail_plan AS (<br> select  customer_id, <br>   start_date as trail_dates<br>    from subscriptions<br>    where plan_id=0<br>),<br>annual_plan as (<br> select  customer_id, <br>   start_date as annual_dates<br>    from subscriptions<br>    where plan_id=3<br>)<br>select <br> ROUND(AVG(DATEDIFF(annual_dates, trail_dates)),0) AS avg_upgrade<br>from annual_plan ap join trail_plan tp<br>ON ap.customer_id = tp.customer_id;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/213/1*qI-dT1vOkMNtEDl_exymSA.png" /></figure><p>The SQL query calculates the average number of days for a customer to upgrade to an annual plan from their initial trial plan on Foodie-Fi, yielding an average of 105 days.</p><p><strong>10. Can you further breakdown this average value into 30 day periods (i.e. 0–30 days, 31–60 days etc).</strong></p><pre>WITH annual_plan AS (<br> SELECT<br>  customer_id,<br>        start_date AS annual_date<br> FROM subscriptions<br>    WHERE plan_id = 3),<br>trial_plan AS (<br> SELECT<br>  customer_id,<br>        start_date AS trial_date<br> FROM subscriptions<br>    WHERE plan_id = 0<br>),<br>day_period AS (<br>SELECT<br> DATEDIFF(annual_date, trial_date) AS diff<br>FROM trial_plan tp LEFT JOIN annual_plan ap <br>ON tp.customer_id = ap.customer_id<br>WHERE annual_date is not null<br>),<br>bins AS (<br>SELECT<br> *, <br>    FLOOR(diff/30) AS bins<br>FROM day_period)<br>SELECT<br> CONCAT((bins * 30) + 1, &#39; - &#39;, (bins + 1) * 30, &#39; days &#39;) AS days,<br> COUNT(diff) AS total<br>FROM bins<br>GROUP BY bins;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/278/1*up45AIpHraSxCGpStkVgKA.png" /></figure><p>The SQL query further breaks down the average upgrade period into 30-day intervals, indicating the number of customers falling within each range.</p><p>It utilizes Common Table Expressions (CTEs) to isolate the start dates of customers’ annual plans (plan_id = 3) and initial trial plans (plan_id = 0), then calculates the difference in days between these dates.</p><p>Subsequently, it categorizes these differences into 30-day bins and determines the count of customers within each bin.</p><p>The result presents the intervals in days and the respective customer counts falling within each range, providing a detailed breakdown of the upgrade periods on Foodie-Fi.</p><p><strong>11. How many customers downgraded from a pro monthly to a basic monthly plan in 2020?</strong></p><pre>WITH next_plan AS (<br> SELECT <br>  *,<br>  LEAD(plan_id, 1) OVER(PARTITION BY customer_id ORDER BY start_date, plan_id) AS plan<br> FROM subscriptions)<br>SELECT<br> COUNT(DISTINCT customer_id) AS num_downgrade<br>FROM next_plan np LEFT JOIN plans p <br>ON p.plan_id = np.plan_id<br>WHERE p.plan_name = &#39;pro monthly&#39; AND np.plan = 1 AND start_date &lt;= &#39;2020-12-31&#39;;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/228/1*XKjYD-CF7YD52q_kemFzxA.png" /></figure><p>The SQL query examines customer downgrades from a pro monthly to a basic monthly plan in 2020.</p><p>By utilizing a Common Table Expression (CTE) to monitor subsequent plans, it identifies cases where customers shifted from a pro monthly plan to a basic monthly plan before or on December 31, 2020.</p><p>However, the analysis reveals that no customers downgraded during this timeframe.</p><p><strong>For more detail visit my </strong><a href="https://github.com/abdullahzunorain/SQL-case-study_Food-Fi"><strong><em>repository</em></strong></a><strong>.</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2a4a89317497" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>