Member-only story
Essential SQL Queries That Data Analysts Shouldn’t Have Missed — Part 2
SQL analysis of some common cases in business
Introduction
In my previous post, Essential SQL Queries That Data Analysts Should Have Known, I wrote about some basic cases and functions that save analysts a little time doing their tasks. In this article, I will continue to share such more cases, but at an intermediate level. To note, throughout this article, SQLite is the primary language for usage.
Identifying active customers
When working with user/customer analysis, the number of active customers is one of many important metrics we care about.
In this case, I will define an active customer as the one who has purchased a company’s product once before and has returned to make another purchase within 7 days.
But first, I will create a simple data table containing information about a company’s orders: order number, customer id, item description, and date of order purchased. The order number is the primary key of this table.
DROP TABLE IF EXISTS `orders_data`;CREATE TABLE `orders_data` (
`ORDER_NO` INTEGER(5) NOT NULL,
`CUSID` INTEGER(4) NOT NULL,
`ITEM` varchar(30) NOT NULL,
`ORDER_DATE`…
