使用工具: pandas
用pandas分析，先将所有表读取为dataframe。转换几个可能用到的时间戳。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
| db_path = r"starsea_capital_ultimate.db"
import sqlite3
import pandas as pd
from decimal import Decimal
import networkx as nx
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
table_names = [table[0] for table in cursor.fetchall()]
dataframes = {}
for table_name in table_names:
query = f"SELECT * FROM {table_name};"
dataframes[table_name] = pd.read_sql_query(query, conn)
conn.close()
dataframes['raw_user_data']['register_time'] = pd.to_datetime(dataframes['raw_user_data']['register_time'], format='%Y-%m-%d %H:%M:%S')
dataframes['raw_user_data']['last_login_time'] = pd.to_datetime(dataframes['raw_user_data']['last_login_time'], format='%Y-%m-%d %H:%M:%S')
dataframes['financial_transactions']['trans_time'] = pd.to_datetime(dataframes['financial_transactions']['trans_time'], format='%Y-%m-%d %H:%M:%S')
def to_decimal(x):
return Decimal(str(x))
|
9、分析数据库检材，直接推荐了最多下线用户共推荐了多少位下线。（答案格式：仅数字）
答案: 17
统计raw_user_data中recommender_id出现的次数。
1
| dataframes['raw_user_data']['recommender_id'].value_counts()
|
1
2
3
4
5
6
7
8
9
10
11
12
13
| recommender_id
853
U10009 17
U10039 17
U10042 16
U10021 16
...
U11381 1
U11185 1
U11889 1
U10524 1
U12588 1
Name: count, Length: 1199, dtype: int64
|
10、分析数据库检材，用户等级为”合伙人”的用户USDT的总投资金额为？（答案格式1234.12，保留2位小数）
答案: 1135581.44
1
2
3
4
5
| h_list = dataframes['raw_user_data'].query(' user_level == "合伙人" ')['user_id'].copy()
trans_df = dataframes['financial_transactions'].query(' trans_type=="投资" & status=="成功" & currency=="USDT" ').copy()
df = trans_df[trans_df['user_id'].isin(h_list)].copy()
df['amount_dec'] = df['amount'].apply(to_decimal)
df['amount_dec'].sum()
|
11、分析数据库检材，平台在2022年度的平均每季度新增用户数为多少？（答案格式：仅数字）
答案: 623
筛选注册时间2022年的用户，resample为季度，计数并计算平均值。
1
2
| df_2022 = dataframes['raw_user_data'][dataframes['raw_user_data']['register_time'].dt.year == 2022].copy()
df_2022.set_index('register_time').resample('QE')['user_id'].count().mean()
|
12、分析数据库检材，在用户推荐关系网络中，层级最深的用户链条包含_多少个用户。（答案格式：仅数字）
答案：7
使用NetworkX建图，计算图中最长路径的长度。
1
2
3
4
5
6
7
8
9
| edge_df = dataframes['raw_user_data'][['user_id', 'recommender_id']].copy()
edge_df = edge_df[edge_df['recommender_id']!='']
G = nx.from_pandas_edgelist(
edge_df,
source='recommender_id',
target = 'user_id',
create_using=nx.DiGraph()
)
len(nx.dag_longest_path(G))
|
13、分析数据库检材，该平台用户在同一时间内同时进行2笔以上大额交易（单笔数值>5000）的异常用户有多少人？（答案格式：仅数字）
答案: 8
查询成功且交易数值大于5000的数据，按时间和用户名groupby，筛选2以上的。
1
2
3
| df = dataframes['financial_transactions'].query(' amount > 5000 & status == "成功" ').copy()
df_count = df.groupby(['user_id', 'trans_time']).size()
df_count[df_count>=2]
|
1
2
3
4
5
6
7
8
9
10
| user_id trans_time
U10211 2022-02-22 2
U10531 2023-07-01 2
U11465 2023-01-29 2
U11583 2023-09-29 2
U11670 2023-10-17 2
U11841 2023-03-05 2
U12374 2022-10-28 2
U12511 2023-07-03 2
dtype: int64
|
14、分析数据库检材，2023年10月11日之前的90天内无任何交易且此前为活跃用户（注册后90天内有过交易）的潜在失效用户有多少？（答案格式：仅数字）
答案: 662
merge用户和交易记录表，筛选注册后90天内有交易的用户为集合1，筛选2023年10月11日之前的90天有交易的用户为集合2，计算集合1-集合2的用户数。
1
2
3
4
5
6
7
8
9
10
11
12
13
| df_trans = dataframes['financial_transactions'].query(' status == "成功" ')[['user_id', 'trans_time']]
df_user = dataframes['raw_user_data'][['user_id', 'register_time']]
merged_df = pd.merge(df_trans, df_user, on='user_id', how='inner')
active_mask = (merged_df['trans_time'] <= merged_df['register_time'] + pd.Timedelta(days=90))
set1 = set(merged_df.loc[active_mask, 'user_id'])
base_date = pd.to_datetime('2023-10-11')
recent_date = base_date - pd.Timedelta(days=90)
recent_mask = ((merged_df['trans_time'] >= recent_date) & (merged_df['trans_time'] < base_date))
set2 = set(merged_df.loc[recent_mask, 'user_id'])
len(set1 - set2)
|