Category: 3 SPL Desktop Analysis

There is a sales data table that records each salesperson’s annual sales amount. 1. Find the years when salesperson No. 5 achieved a growth in sales amount In Excel, first filter for data where the SELLERID is 5, and then determine whether the annual sales amount has increased each year. Filter for data where the SELLERID is 5 and copy it out. Determining whether there is growth is not easily achieved directly through the menu functions, so you can add a helper column to indicate the growth status and then filter again. Add a helper column, enter the formula in

The following is part of the data from the simplified purchases table ‘purchases’ and sales table ‘sales’: sales: purchases: Download test data 1. Calculate the number of weeks each product experienced stock-out It is required to count only the week in which a product becomes stock-out; if a product remains stock-out for multiple consecutive weeks, subsequent weeks are excluded from the count. The approach to solving this problem is as follows: First, treat purchases as inventory increases and sales as inventory decreases. Therefore, we convert the sales quantities to negative numbers. Next, union the two tables and sort by product

The following is part of the data from the simplified account table ‘user’ and access table ‘view’: user: view: Download test data 1. Calculate the number of minutes each account spent accessing each product Group by account id and product, and then aggregate the number of minutes of each group. The results contain the columns account id, product and number of minutes. We want to pivot the results to the following format: account id, product A minutes, product B minutes, product C minutes, and so on. SQL code: In SQL, you need to define separate aggregations for each product. Since

Data structure Medical visit records table (Appointments): appid is the medical visit ID, patientid is the patient ID, depid is the department ID, and appdate is the medical visit date. 1. Query for the times when the number of visits reached a multiple of 50,000 in 2020 and 2021, respectively The expected target results are shown in the figure below: SQL statement: While the window function is used, the SQL statement still needs to nest multiple layers of code, which is a bit cumbersome. This is mainly because SQL’s result sets are unordered, requiring the addition of implicit markers to

Data structure and sample data: The table stores data for flights departing from and arriving at city airports on January 1, 2025. In this table, FId is the flight number, AircraftId is the aircraft number, DepCity is the departure city, ArrCity is the arrival city, DepTime is the departure time, and ArrTime is the arrival time. At 0:00 on that day,some aircraft were already parked in these cities. For example, the first record in the table indicates that the aircraft with AircraftId 1 was already in Washington at the start of the day. In this case, FId, DepCity, and DepTime

The following is part of the data from the simplified user behavior table actions, which records the occurrence time of 9 types of events for each user: Download test data 1.Find users whohave sequentially triggered events e2, e3, e7 Sequential events do not require continuity. First, group the events by user. Then, sort each user’s events by time. Next, loop through each user’s e2 event to search for an e3 event after it. If an e3 is found, search for an e7 event after e3. If an e7 is found, the user is deemed to satisfy the criteria. Let’s look

Excel is an essential tool for daily work, offering quick and convenient data processing, making it very popular. However, its data processing capabilities are not limitless, and when confronted with big data, Excel’s performance becomes inadequate. For data with several hundred thousand rows, just opening such a large file in Excel will take several minutes. If you perform more complex calculations, then it will become very slow, or even crash. When the data volume exceeds one million rows, Excel will truncate the data due to capacity limitations, making it unprocessable. Therefore, Excel is no longer a suitable tool for big

In daily work, when it comes to data calculation and analysis, Excel is definitely the most frequently used tool. However, many people are also confused by some issues of Excel, such as the difficulty of in-depth learning of Excel, the challenges of tackling complex problems, the repetitiveness of solving the same problems, and the inability to handle big data. In this article, we will discuss Excel’s advantages and disadvantages in data analysis, and how to overcome such issues. Let’s first look at the advantages of Excel. 1. Simple, easy to learn and get started Firstly, Excel offers many function menus,

Using SQL for data anlysis is actually quite basic SQL is widely used for data analysis and often considered a default skill of data analysts. Indeed, being able to write SQL in a database environment is very convenient; it might seem as if you can solve any query by just writing a single SQL statement. For example, when you want to find sales grouped by user, the SQL code written out is as simple as English: However, data analysis tasks are not always that simple. For example, to calculate the next-day retention rate of new users, the SQL code will

Learn programming Starting from SPL Programming, here is video course. This book is aimed for non professionals and does not require programming experience. A high school math foundation is sufficient.Chapters 1-4 introduce basic program logic (variables, branches, loops, etc.), which is also included in most programming languages. Except for Section 4.4 Understanding object, which has some depth and requires more time to understand, the learning difficulty of other parts is not high, and guys with Excel foundation can easily master them.Chapter 5 begins to introduce some unique styles of SPL programming, which are different from other programming languages. After understanding