Category: 3 SPL Desktop Analysis

Various data operations, such as filtering, grouping, sorting and summarization, constitute a data analyst’s everyday work. SQL is really handy for handling these basic needs, such as calculating sales amount for users meeting the specified criteria and finding VIP customers meeting the specified condition. As the following shows, the SQL statement for implementing such a task is like an English sentence. It is simple and efficient: It seems that SQL is the synonym of “simple and efficient”. The language queries data directly and returns result directly – this is one of the reasons that it has become the standard data

The dilemma of “strong computing ability vs high interactivity” Excel and BI are commonly used data analysis tools, and both are suitable for handling preliminary data analysis tasks, such as getting total sales in each month and finding average order amount and purchase frequency in each group. As business requirements grow, more complex tasks are hard to be handled with Excel or BI. Examples include finding intervals when a stock rises consecutively for at least 5 days, counting users who are active for 3 days continuously in every 7 days, and finding the distribution of numbers of users for 3-day,

SQL is wasting lives of data scientists SQL is difficult to write Nearly all data scientists use SQL for data exploration and analysis. SQL appears deceptively simple and offers a certain degree of interactivity, making it a seemingly ideal choice for these purposes. To perform filtering and grouping operations, for example, SQL just needs one line of code: But this is only limited to simple cases. When the computation becomes complex, SQL code becomes complicated, too. For example, to count the longest consecutive rising days of each stock, the SQL statement is as follows: And to perform the commonly seen

01 – User Session Count User behavior data table userid action_type action_time U1059 login 2023-12-01 18:00:10 U1092 login 2023-12-01 18:00:17 U1069 login 2023-12-01 18:00:22 … … … A session is considered over if a user does not take any action within 10 minutes, or if they do not log in within 5 minutes after logging out. Calculate the number of sessions for each user. Copy Code：https://c.scudata.com/article/1729475229196#toc_h3_1 02 – Count the players who score 3 times in a row within 1 minute Score table of a ball game team player play_time score A A3 2023-12-31 09:00:09 2 B B1 2023-12-31 09:00:24

Sales personnel are highly skilled at using Excel for routine analysis. However, when data preparation steps become cumbersome, the problems to be solved are relatively complex, and tasks are repetitive, relying solely on Excel’s internal techniques can be quite difficult. The ideal approach is to solve these issues using external programs or tools. If you’ve ever explored and practiced this, you’ll find that popular programming languages or tools often fall short. For example, Python has a high learning curve, is not user-friendly for non-IT professionals, and is not suitable for typical business calculations. Similarly, Power Query, with its graphical interface

Quantitative trading involves using probability theory, statistical and other knowledges through programming and modeling to analyze massive amount of historical data, identify patterns and develop quantitative models, and then relying on computers’ powerful computing capabilities to make trading decisions fast and efficiently. There are many programming languages to choose from. The chart below is from the TIOBE index, showing the top 10 programming languages from 2002 to the present. It can be seen that, for a long period, Java and C dominated the rankings, but since 2018, with the rise of artificial intelligence, Python’s status has risen rapidly and now

In contemporary business environment, data analytics has already become an important decision-making tool. It enables businesses to better understand market trend, user behaviors and internal operations, and thereby develop well-founded strategies and become more competitive. But data analytics is not easy. You need to choose a suitable tool and method. Many prefer to choose BI tools because Business Intelligence sounds closely related to data analytics, and the software appears very suitable for the job with its smooth interactivity and gorgeous UI most of the time. Yet BI tools have been technologically narrowed down to multidimensional analysis that mainly focuses on

When making reports, there is often a need to summarize data by self-defined statistical intervals such as time period, numerical range. For example, we may need to count the number of employees in different age groups by department based on employee data. The employee data is stored in an xls file: We want to achieve the following result: In Excel, we can use the COUNTIFS function to implement conditional aggregation, such as the formula in C4: =COUNTIFS(report!$H$2:$H$500,A4,report!$J$2:$J$500,“>=30”,report!$J$2:$J$500,“<40”). However, since what the original data stores is the birth dates, and we need to count by age group here, it needs to

When conducting data analysis, there will often be some data comparison tasks, which need to associate the data of multiple tables for display. For example, we have two xls tables that contain the sales data and the cash collection data of each state respectively. Here below is the Sales data table: Cash collection data table: Now we want to see a comparison table like this: Should we use Excel to carry out this task, we would need to create a pivot table for each of the two tables, and use VLOOKUP to associate the two tables on States. However, for

Excel is currently the most widely used spreadsheet tool in the workplace, with a large number of professionals in various industries using it to draw tables and perform data analysis. Without Excel, many workplace personnels cannot work properly. However, Excel is not perfect either. Its automation ability is relatively weak, and the implementation of tables with complex formats is too cumbersome or impossible. Tables with simple formats that involve frequent data changes can also bring a lot of workloads, becoming a nightmare for an Excelman. Working overtime is unavoidable. So, let’s take a look at how to use the report