find-users-with-persistent-behavior-patterns

java

python3

python

javascript

typescript

csharp

golang

kotlin

swift

rust

ruby

dart

scala

elixir

erlang

racket

cangjie

bash

mysql

mssql

postgresql

oraclesql

pythondata

Database

Table: <code>activity</code>

<pre>
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| user_id | int |
| action_date | date |
| action | varchar |
+--------------+---------+
(user_id, action_date, action) is the primary key (unique value) for this table.
Each row represents a user performing a specific action on a given date.
</pre>

Write a solution to identify behaviorally stable users based on the following definition:

<ul>
	<li>A user is considered behaviorally stable if there exists a sequence of at least <code>5</code> consecutive days such that:

	<ul>
		<li>The user performed exactly one action per day during that period.</li>
		<li>The action is the same on all those consecutive days.</li>
	</ul>
	</li>
	<li>If a user has multiple qualifying sequences, only consider the sequence with the maximum length.</li>
</ul>

Return the result table ordered by <code>streak_length</code> in descending order, then by <code>user_id</code> in ascending order.

The result format is in the following example.

&nbsp;
Example:

<div class="example-block">
Input:

activity table:

<pre class="example-io">
+---------+-------------+--------+
| user_id | action_date | action |
+---------+-------------+--------+
| 1 | 2024-01-01 | login |
| 1 | 2024-01-02 | login |
| 1 | 2024-01-03 | login |
| 1 | 2024-01-04 | login |
| 1 | 2024-01-05 | login |
| 1 | 2024-01-06 | logout |
| 2 | 2024-01-01 | click |
| 2 | 2024-01-02 | click |
| 2 | 2024-01-03 | click |
| 2 | 2024-01-04 | click |
| 3 | 2024-01-01 | view |
| 3 | 2024-01-02 | view |
| 3 | 2024-01-03 | view |
| 3 | 2024-01-04 | view |
| 3 | 2024-01-05 | view |
| 3 | 2024-01-06 | view |
| 3 | 2024-01-07 | view |
+---------+-------------+--------+
</pre>

Output:

<pre class="example-io">
+---------+--------+---------------+------------+------------+
| user_id | action | streak_length | start_date | end_date |
+---------+--------+---------------+------------+------------+
| 3 | view | 7 | 2024-01-01 | 2024-01-07 |
| 1 | login | 5 | 2024-01-01 | 2024-01-05 |
+---------+--------+---------------+------------+------------+
</pre>

Explanation:

<ul>
	<li>User 1:

	<ul>
		<li>Performed <code>login</code> from 2024-01-01 to 2024-01-05 on consecutive days</li>
		<li>Each day has exactly one action, and the action is the same</li>
		<li>Streak length = 5 (meets minimum requirement)</li>
		<li>The action changes on 2024-01-06, ending the streak</li>
	</ul>
	</li>
	<li>User 2:
	<ul>
		<li>Performed <code>click</code> for only 4 consecutive days</li>
		<li>Does not meet the minimum streak length of 5</li>
		<li>Excluded from the result</li>
	</ul>
	</li>
	<li>User 3:
	<ul>
		<li>Performed <code>view</code> for 7 consecutive days</li>
		<li>This is the longest valid sequence for this user</li>
		<li>Included in the result</li>
	</ul>
	</li>
</ul>

The Results table is ordered by streak_length in descending order, then by user_id in ascending order
</div>

find_behaviorally_stable_users

Find Users with Persistent Behavior Patterns

表：<code>activity</code>

<pre>
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| user_id | int |
| action_date | date |
| action | varchar |
+--------------+---------+
(user_id, action_date, action) 是这张表的主键（值互不相同）。
每一行代表一个用户在特定日期执行的具体操作。
</pre>

根据以下定义，编写一个解决方案来识别 行为稳定的用户：

<ul>
	<li>一个用户如果存在一个 连续至少&nbsp;<code>5</code>&nbsp;天的行为序列满足以下条件，则认为他是 行为稳定&nbsp;的：

	<ul>
		<li>该用户在该期间 每天只执行了一个操作。</li>
		<li>这些连续的日子里，操作都是相同的。</li>
	</ul>
	</li>
	<li>如果一个用户有多个符合条件的序列，只考虑 最长 的那条序列。</li>
</ul>

返回结果表按&nbsp;<code>streak_length</code> 降序&nbsp;排序，然后按&nbsp;<code>user_id</code> 升序&nbsp;排序。

结果格式如下所示。

&nbsp;

示例：

<div class="example-block">
输入：

activity 表：

<pre class="example-io">
+---------+-------------+--------+
| user_id | action_date | action |
+---------+-------------+--------+
| 1 | 2024-01-01 | login |
| 1 | 2024-01-02 | login |
| 1 | 2024-01-03 | login |
| 1 | 2024-01-04 | login |
| 1 | 2024-01-05 | login |
| 1 | 2024-01-06 | logout |
| 2 | 2024-01-01 | click |
| 2 | 2024-01-02 | click |
| 2 | 2024-01-03 | click |
| 2 | 2024-01-04 | click |
| 3 | 2024-01-01 | view |
| 3 | 2024-01-02 | view |
| 3 | 2024-01-03 | view |
| 3 | 2024-01-04 | view |
| 3 | 2024-01-05 | view |
| 3 | 2024-01-06 | view |
| 3 | 2024-01-07 | view |
+---------+-------------+--------+
</pre>

输出：

<pre class="example-io">
+---------+--------+---------------+------------+------------+
| user_id | action | streak_length | start_date | end_date |
+---------+--------+---------------+------------+------------+
| 3 | view | 7 | 2024-01-01 | 2024-01-07 |
| 1 | login | 5 | 2024-01-01 | 2024-01-05 |
+---------+--------+---------------+------------+------------+
</pre>

解释：

<ul>
	<li>用户 1：

	<ul>
		<li>从 2024 年 1 月 1 日至 2024 年 1 月 5 日连续五天执行&nbsp;<code>login</code>&nbsp;操作</li>
		<li>每一天都恰好有一个操作，且操作相同</li>
		<li>连续长度 = 5（满足最小要求）</li>
		<li>行动在 2024-01-06 发生变化，结束连续计数</li>
	</ul>
	</li>
	<li>用户 2：
	<ul>
		<li>只连续执行了 4 天&nbsp;<code>click</code>&nbsp;操作</li>
		<li>不满足最小连续计数 5 天的要求</li>
		<li>从结果排除</li>
	</ul>
	</li>
	<li>用户 3：
	<ul>
		<li>连续 7 天执行了&nbsp;<code>view</code>&nbsp;操作</li>
		<li>这是此用户的最长有效序列</li>
		<li>包含在结果中</li>
	</ul>
	</li>
</ul>

结果表按 streak_length 降序排序，然后按 user_id 升序排序。
</div>