AB TEST

最新推荐文章于 2025-10-11 15:53:34 发布

原创最新推荐文章于 2025-10-11 15:53:34 发布 · 515 阅读

0 ·

CC 4.0 BY-SA版权

本报告详细分析了游戏内购活动的表现，通过SQL查询，对比了不同时间段内玩家的消费行为，包括支付总额、购买次数及平均消费额，为游戏运营提供数据支持。

五号桶到现在11天

with rank as(
SELECT group_id, SUM(pay_dollar) as pay_dollars, count(*) as count FROM
(select mod(player_id, 33) as group_id,  pay_amount*exchange as pay_dollar FROM mafia1.offer_purchase
WHERE timestamp >='2019-10-19 03:10:00' and timestamp <='2019-10-30 06:10:00' and player_id not in (select player_id FROM mafia1.create_player where uid in (select uid from mafia1.internal_user)))
# and player_id in (select player_id FROM mafia1.create_player where timestamp >='2019-07-01'))
#where pay_dollar >=90
group by group_id
order by pay_dollars)

select group_id, pay_dollars/total_dollars as ratio, count, pay_dollars, pay_dollars/count as mean FROM rank, (select SUM(pay_dollars) as total_dollars FROM rank)
Order by pay_dollars

五号桶上线前11天

with rank as(
SELECT group_id, SUM(pay_dollar) as pay_dollars, count(*) as count FROM
(select mod(player_id, 33) as group_id,  pay_amount*exchange as pay_dollar FROM mafia1.offer_purchase
WHERE timestamp >='2019-10-06 03:10:00' and timestamp <='2019-10-17 01:30:00' and player_id not in (select player_id FROM mafia1.create_player where uid in (select uid from mafia1.internal_user)))
# and player_id in (select player_id FROM mafia1.create_player where timestamp >='2019-07-01'))
#where pay_dollar >=90
group by group_id
order by pay_dollars)

select group_id, pay_dollars/total_dollars as ratio, count, pay_dollars, pay_dollars/count as mean FROM rank, (select SUM(pay_dollars) as total_dollars FROM rank)
Order by pay_dollars

２９号桶到现在７天

with rank as(
SELECT group_id, SUM(pay_dollar) as pay_dollars, count(*) as count FROM
(select mod(player_id, 33) as group_id,  pay_amount*exchange as pay_dollar FROM mafia1.offer_purchase
WHERE timestamp >='2019-10-24 06:10:00' and timestamp <='2019-10-30 06:10:00' and player_id not in (select player_id FROM mafia1.create_player where uid in (select uid from mafia1.internal_user)))
# and player_id in (select player_id FROM mafia1.create_player where timestamp >='2019-07-01'))
#where pay_dollar >=90
group by group_id
order by pay_dollars)

select group_id, pay_dollars/total_dollars as ratio, count, pay_dollars, pay_dollars/count as mean FROM rank, (select SUM(pay_dollars) as total_dollars FROM rank)
Order by pay_dollars

２９号桶上线前７天

with rank as(
SELECT group_id, SUM(pay_dollar) as pay_dollars, count(*) as count FROM
(select mod(player_id, 33) as group_id,  pay_amount*exchange as pay_dollar FROM mafia1.offer_purchase
WHERE timestamp >='2019-10-18 06:10:00' and timestamp <='2019-10-24 06:10:00' and player_id not in (select player_id FROM mafia1.create_player where uid in (select uid from mafia1.internal_user)))
# and player_id in (select player_id FROM mafia1.create_player where timestamp >='2019-07-01'))
#where pay_dollar >=90
group by group_id
order by pay_dollars)

select group_id, pay_dollars/total_dollars as ratio, count, pay_dollars, pay_dollars/count as mean FROM rank, (select SUM(pay_dollars) as total_dollars FROM rank)
Order by pay_dollars