<?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 Bharati V on Medium]]></title>
        <description><![CDATA[Stories by Bharati V on Medium]]></description>
        <link>https://medium.com/@bharativ76?source=rss-1160dc72f0ac------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*amAmilw3NfOFOEbf</url>
            <title>Stories by Bharati V on Medium</title>
            <link>https://medium.com/@bharativ76?source=rss-1160dc72f0ac------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 17:30:16 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@bharativ76/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[Navigating the HackerRank SQL Advanced Certification: Key Questions and Answers]]></title>
            <link>https://medium.com/@bharativ76/navigating-the-hackerrank-sql-advanced-certification-key-questions-and-answers-a5c9f70ec896?source=rss-1160dc72f0ac------2</link>
            <guid isPermaLink="false">https://medium.com/p/a5c9f70ec896</guid>
            <dc:creator><![CDATA[Bharati V]]></dc:creator>
            <pubDate>Sat, 10 May 2025 15:21:10 GMT</pubDate>
            <atom:updated>2025-05-10T15:21:10.627Z</atom:updated>
            <content:encoded><![CDATA[<blockquote>I recently earned the HackerRank SQL Advanced Certification, and I’m thrilled to share my journey with you. In this article, I’ll guide you through two exam questions, providing detailed solutions and explanations. Whether you’re preparing for this certification or looking to boost your SQL skills, this article is for you!</blockquote><p><strong>Q.1</strong> <strong>The times that employees log in and out are recorded over the course of a month. For each employee, determine the number of hours worked during the weekends. For simplicity, hours worked in a day, hours are truncated to their integer part.<br>For example, there are 10 hours between ‘2000:01:01 00:45:00’ and ‘2000:01:01 10:45:00’. There are 9 hours between ‘2000:01:01 00:46:00’ and ‘2000:01:01 10:45:00’.<br>Return a list of employee IDs and weekend hours worked, descending by hours worked.</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*BP5HOAO3BkBqPM5nUj-sFg.png" /><figcaption>Input Table — attendance</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Y_ZzmDmpY4TrdY2UqqKN7g.png" /><figcaption>Expected Output</figcaption></figure><p>Query for the above case:</p><pre>SELECT emp_id, SUM(work_hours) AS total_weekend_hours<br>FROM (<br>  SELECT <br>    emp_id,<br>    TIMESTAMPDIFF(HOUR, MIN(timestamp), MAX(timestamp)) AS work_hours<br>  FROM attendance<br>  WHERE DAYOFWEEK(timestamp) IN (1, 7)  <br>  GROUP BY emp_id, DATE(timestamp)<br>) AS t<br>GROUP BY emp_id<br>ORDER BY total_weekend_hours DESC;</pre><p>Let’s break down this query and explain it step by step.<br><strong>Step 1: Identify Weekend Records</strong><br>First, we need to filter the records to include only those that fall on weekends (Saturdays and Sundays). In SQL, this is achieved using the DAYOFWEEK function.</p><pre>WHERE DAYOFWEEK(timestamp) IN (1, 7)</pre><ul><li>DAYOFWEEK(timestamp) returns an integer representing the day of the week for a given date (1 for Sunday and 7 for Saturday). By selecting IN (1, 7), we&#39;re filtering out records that occur on weekends.</li></ul><p><strong>Step 2: Partition Data by Employee and Date</strong></p><p>Next, we need to group the data by employee and date to handle all logins and logouts for each person on each weekend day. This allows us to compute total hours for each distinct day.</p><pre>GROUP BY emp_id, DATE(timestamp)</pre><ul><li>This GROUP BY divides the data into partitions based on each emp_id and unique DATE(timestamp), allowing for distinct processing per day.</li></ul><p><strong>Step 3: Compute Daily Work Duration</strong></p><p>Within each group (from Step 2), compute the difference between the earliest and latest timestamps to determine how many hours an employee worked on that specific day.</p><pre>TIMESTAMPDIFF(HOUR, MIN(timestamp), MAX(timestamp)) AS work_hours</pre><ul><li>MIN(timestamp) and MAX(timestamp) find the earliest and latest timestamps for each group, respectively.</li><li>TIMESTAMPDIFF(HOUR, MIN(timestamp), MAX(timestamp)) function calculates total hours worked in a day by taking the earliest (MIN) and latest (MAX) timestamps.</li></ul><p><strong>Step 4: Aggregate and Sort by Total Weekend Hours</strong></p><p>Finally, after computing daily work hours, aggregate these values by employee to find total weekend hours, and sort the results to easily see which employees worked the most during weekends.</p><pre>SELECT emp_id, SUM(work_hours) AS total_weekend_hours<br>FROM (<br>    -- Inner query<br>) AS t<br>GROUP BY emp_id<br>ORDER BY total_weekend_hours DESC;</pre><ul><li>SUM(work_hours) AS total_weekend_hours: aggregates daily hours to give each employee’s total weekend hours, while ORDER BY total_weekend_hours DESC sorts employees from most to least total weekend hours worked.</li></ul><p><strong>Q.2</strong> <strong>A number of algorithms are used to mine cryptocurrencies. As a part of comparison, create a query to return a list of algorithms and their volumes to each quarter of year 2020.</strong></p><p><strong>The results should be in the following format: algorithm name Q1, Q2, Q3,Q4 transactions.</strong></p><ul><li><strong>Q1 through Q4 contain the sum of transaction volumes for the algorithm for each calendar quarter of 2020 precise to 6 places after the decimal.</strong></li><li><strong>Results should be sorted ascending by algorithm name.</strong></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*AeFYMjwxrFbA_fuvP3QrFw.png" /><figcaption>Input Data</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*AOyZHKYuL--fyxbPSOwqHQ.png" /><figcaption>Expected Output</figcaption></figure><p>Query for the above case:</p><pre>WITH Quaters AS (<br>    SELECT<br>    c.algorithm,<br>    Quarter(dt) as Qp,<br>    SUM(t.volume) as Vol<br>    FROM<br>    coins c INNER JOIN transactions t ON c.code = t.coin_code<br>    WHERE<br>    YEAR(t.dt) = 2020<br>    GROUP BY<br>    c.algorithm, Quarter(dt)<br>)<br><br>SELECT <br>Q1.algorithm as algorithm,<br>Q1.Vol as transactions_Q1,<br>Q2.Vol as transactions_Q2,<br>Q3.Vol as transactions_Q3,<br>Q4.Vol as transactions_Q4<br> FROM Quaters Q1 <br>    LEFT JOIN Quaters Q2 ON Q1.algorithm = Q2.algorithm AND Q2.Qp = 2<br>    LEFT JOIN Quaters Q3 ON Q1.algorithm = Q3.algorithm AND Q3.Qp = 3<br>    LEFT JOIN Quaters Q4 ON Q1.algorithm = Q4.algorithm AND Q4.Qp = 4<br>WHERE<br>    Q1.Qp = 1<br>ORDER BY<br>    Q1.algorithm;</pre><p><strong>Step 1: Define Quarterly Transaction Volumes</strong></p><p>First, we create a Common Table Expression (CTE) named Quarters to calculate the total transaction volumes per coin algorithm for each quarter of the year 2020.</p><pre>WITH Quarters AS (<br>    SELECT<br>    c.algorithm,<br>    Quarter(dt) as Qp,<br>    SUM(t.volume) as Vol<br>    FROM<br>    coins c INNER JOIN transactions t ON c.code = t.coin_code<br>    WHERE<br>    YEAR(t.dt) = 2020<br>    GROUP BY<br>    c.algorithm, Quarter(dt)<br>)</pre><ul><li><strong>Explanation</strong>:</li><li><strong><em>Coin-Transaction Join</em></strong><em>:</em> The query joins the coins table with the transactions table on the coin code to associate each transaction with its respective coin algorithm.</li><li><strong><em>Quarter and Year Filter</em></strong><em>:</em> It filters transactions that occurred in the year 2020 and calculates the quarter of each transaction date using Quarter(dt).</li><li><strong><em>Group and Sum</em></strong><em>:</em> Groups the data by algorithm and quarter, calculating the total transaction volume (SUM(t.volume)) for each algorithm-quarter pair.</li></ul><p><strong>Step 2: Assemble Quarterly Data Across Algorithms</strong></p><p>Now, the main query retrieves the transaction volumes for all four quarters, arranging them side by side for each algorithm.</p><pre>SELECT <br>Q1.algorithm as algorithm,<br>Q1.Vol as transactions_Q1,<br>Q2.Vol as transactions_Q2,<br>Q3.Vol as transactions_Q3,<br>Q4.Vol as transactions_Q4<br> FROM Quarters Q1 <br>    LEFT JOIN Quarters Q2 ON Q1.algorithm = Q2.algorithm AND Q2.Qp = 2<br>    LEFT JOIN Quarters Q3 ON Q1.algorithm = Q3.algorithm AND Q3.Qp = 3<br>    LEFT JOIN Quarters Q4 ON Q1.algorithm = Q4.algorithm AND Q4.Qp = 4<br>WHERE<br>    Q1.Qp = 1<br>ORDER BY<br>    Q1.algorithm;</pre><p><strong>Explanation</strong>:</p><p><strong><em>1.Quarterly Joins</em>: </strong>The main query takes the Quarters CTE and uses a series of LEFT JOINs to align the volumes for each quarter (Q1 through Q4).</p><ul><li>Each LEFT JOIN matches on the algorithm and specifies the quarter, ensuring each quarter&#39;s data is linked and displayed if available.</li></ul><p><strong><em>2.Initial Quarter Filtering</em></strong><em>:</em> The WHERE Q1.Qp = 1 condition ensures that only records corresponding to the first quarter are used as the base for joins, and hence comparisons are aligned correctly.<br><strong><em>3.Ordering</em></strong><em>:</em> The results are ordered by the algorithm to organize the output neatly.</p><p><strong>Step 3: Produce Algorithm-Based Quarterly Summary</strong></p><p>This step provides an overview of transaction volumes per quarter for each algorithm, with algorithms sorted alphabetically.</p><p><strong>Result Presentation</strong>:</p><ul><li>Each row represents one algorithm.</li><li>Columns show transaction volumes for each quarter (Q1 to Q4). If no transactions occurred in a particular quarter, the corresponding column for that quarter may contain NULL.</li></ul><blockquote>Strategies for Succeeding in the HackerRank Advanced SQL Certification</blockquote><p><strong>1.Master Window Functions</strong>: Delve deep into functions like RANK(), DENSE_RANK(), and ROW_NUMBER(). Develop proficiency with the OVER() clause and its capabilities, especially using ORDER BY and PARTITION BY, to perform advanced data analysis.</p><p><strong>2.Excel in Aggregation Queries</strong>: Strengthen your grasp on GROUP BY, HAVING, and aggregate functions like SUM(), MAX(), COUNT(), and AVG(). Aim to apply these techniques effectively with various filtering and sorting conditions to derive meaningful insights.</p><p><strong>3.Consider Edge Cases</strong>: Always factor in unique scenarios, such as:</p><ul><li>Handling tables that lack rows (e.g., testing the behavior of your queries on an empty dataset).</li><li>Addressing data duplicates (e.g., discerning distinct values or ensuring correct rankings when dealing with ties).</li></ul><blockquote><strong>Final Thoughts</strong>: Earning the HackerRank Advanced SQL Certification is a remarkable way to highlight your SQL skills. The questions challenged both my technical abilities and my critical thinking capacity. I hope this guide aids you on your certification journey. Feel free to share your experiences or raise any questions in the comments below!</blockquote><p>Best of luck, and enjoy your querying journey!😊</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a5c9f70ec896" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Power BI 101: A beginner’s guide to Power BI.]]></title>
            <link>https://medium.com/@bharativ76/power-bi-101-a-beginners-guide-to-power-bi-c3e52d7e8d79?source=rss-1160dc72f0ac------2</link>
            <guid isPermaLink="false">https://medium.com/p/c3e52d7e8d79</guid>
            <dc:creator><![CDATA[Bharati V]]></dc:creator>
            <pubDate>Tue, 09 Jan 2024 18:48:48 GMT</pubDate>
            <atom:updated>2024-01-09T18:48:48.822Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/474/0*3T7o_ifhSvZ6N15J" /></figure><p>Power BI’s interface initially struck me as identical to other Microsoft Office products. If you’re looking to create visually appealing and data-rich dashboards, Power BI is a compelling alternative to Tableau, even though Tableau has its unique features and advantages.</p><p><em>Power BI offers a wide range of tools and functions for data analysis, including drilldowns, data slicing and dicing, and the ability to create custom visuals. These tools enable users to deeply explore their data and discover intricate relationships and patterns. In this post, I’ll walk you through how to get started with Power BI, covering key components of Power BI and data integration, as well as the fundamentals of building a dashboard for data visualization. Power BI is one of the most widely used business intelligence and analytics solutions globally, but many people misunderstand Power BI products, particularly the desktop and web versions.</em></p><p><strong>Power BI Desktop and Power BI Service</strong>: What’s the difference?</p><p><strong>Power BI Desktop</strong> is a free, on-premises (Windows) version of Power BI that provides data analysis and report creation capabilities. With Power Query Editor, you can connect to various data sources, transform data into a model, and create reports on your local computer. The desktop version primarily does not require an internet connection to work.</p><p>On the other hand, <strong>Power BI Service</strong> is the online version of Power BI Desktop. It allows you to access your locally created dashboard from a Power BI desktop or create an entirely new dashboard online. With Power BI Service, you can create and share apps, create dashboards, and explore and analyze data to uncover business insights.</p><p>I use Power BI Desktop for heavy-duty work such as data integration, building data models, and creating a dashboard on my local computer. Then, I use Power BI Service to access my dashboard online and share it with my colleagues. (Demonstration coming up in the next sections)</p><p><strong>Starting with Power BI Desktop</strong></p><p>Now let’s get started.</p><p>‍<strong>Step 1:</strong> Download Power BI Desktop from Microsoft Store</p><p><a href="https://powerbi.microsoft.com/en-in/desktop/">Power BI Desktop — Interactive Reports | Microsoft Power BI</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uh7R23xfP72NPKP2RUGPmA.png" /></figure><p>‍The installation is pretty straightforward.</p><p><strong>Step 2:</strong> Once the Power BI Desktop is installed on your local computer, open a new file in Power BI Desktop. You will see a window like below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*AWpve5Sel58wIAcbk4g7ew.png" /></figure><p>‍</p><p><strong>Step 3:</strong> Select Get data from the Ribbon inside the home section and select your datasource.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NUQVD7AUSUiP9k5p8Tf2PA.png" /></figure><p><strong>Step 4:</strong> Since my project data was stored in Excel, I will use Excel workbook data source to simulate my project report.‍</p><p><strong>Step 5:</strong> Once you select the excel file from your computer as data source next page would be Navigator window like below:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*g-Fne6q7Q8OHqG9dtqgvtw.png" /></figure><p>Here, you will see worksheet available from you source data file, select the required sheet and then you will have to option <strong>Load</strong> or <strong>Transform Data.</strong></p><p>As of now I’ll select Load by considering, that my data is clean and if further needed to do any transformation we can go to Transform Data (ie.Power Query Editor).</p><p><strong>Step 6:</strong> When the data load is complete, you will see your tables on the right-side panel of the window.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nqWZflI_VNCIIkMDgp4M9w.png" /></figure><p><strong>Step 7:</strong> Select the type of chart from the visual gallery under the Home tab. For example, a Pie Chart, then add a legend and values.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ESTh97eBtOCdtEe1IterqA.png" /></figure><p>This is how the pie chart will look in the report. You can also style your pie chart at this point.</p><p>Check out this link — <a href="https://learn.microsoft.com/en-us/power-bi/create-reports/service-dashboards-design-tips">how to style and beautify a Power BI dashboard</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*BQtO1y6GRFMzTUbqZPsXlw.png" /></figure><p>In this way you can create as many required visual and format it as per your choice. Once done with creating report save the file.</p><p><strong>Step 8:</strong> Save the Power BI file.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pbJlKe8wv4iC3yDJHBe5tg.png" /></figure><p>Then publish the dashboard to your Power BI service. By selecting publish button from the Ribbon under the Home section.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/955/1*8DOLP6iqk0w8FJ-t2mjapw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/747/1*0VQUIqkhf9g1omGQYry85g.png" /></figure><blockquote><strong><em>Starting with Power BI Service</em></strong></blockquote><p><strong>Step 9</strong>: Next, Open <a href="https://app.powerbi.com/home?experience=power-bi">Power BI</a>. Sign Into Power BI. Then go to My workspace to access your dashboard.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mDw64xbRmU8llDe_sGOBWA.png" /></figure><p>On your workspace page, you will see two types of files — Report and Dataset.</p><p>First, click on the report, and you will be redirected to your dashboard page. You can edit your dashboard further by clicking the edit button from the menu bar at the top of the window.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/948/1*SS7YAxp7jqLH4gRF3rDu3A.png" /></figure><p>‍Next, go back to your My workspace page and open the dataset file, you will see your tables located at the right side of the window.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/851/1*OcOlQ4iBfOU-d11wNVdWVw.png" /></figure><p>‍</p><p><em>That’s it!</em></p><p>Now that you have a basic understanding of Power BI and its capabilities, I encourage you to explore the tool on your own. Experiment with its interface and create reports and dashboards with the data you have. Don’t hesitate to try out different features and techniques. The more you use Power BI, the more you’ll understand its full potential and become more confident in creating dynamic and engaging data visualizations. Remember, the best way to learn is by doing, so dive into Power BI today!</p><p><strong>Conclusion</strong></p><p>If you’re looking to expand your data visualization skills, Power BI is a powerful tool that can help you create dynamic and interactive reports and dashboards.</p><p>In the upcoming chapter of the Power BI series, you’ll learn more advanced topics such as how to use DAX, how to style dashboards, and how to connect real-time Bigdata with Power BI, which will enhance your ability to analyze, visualize and present data in a meaningful way.‍</p><p>‍</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c3e52d7e8d79" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>