_吉比特笔试题

Kevin201907151131712

selectn sum(b.amt) as total, count(distinct b.userid) as num

from

(select date as regtime, userid

from tableA

where date >= '20161201'

and date <= '20161230') a

left join

(select userid, sum(money) as amt,

from tableB

group by userid) b on a.userid = b.userid

select

date,

count(distinct case when money > 0 and money < 500 then userid end) as "(0,500) fufeirenshu",

sum(case when money > 0 and money < 500 then money end) as "fufei1",

count(distinct case when money >= 500 and money < 5000 then userid end) as "[500,5000)fufeirenshu",

sum(case when money >= 500 and money < 5000 then money end) as "fufei2",

count(distinct case when money >= 5000 then userid end) as "[5000,oo)fufeirenshu",

sum(case when money >= 5000 then money end) as "fufei3"

from tableB

where date >= '20160101'

group by date

order by date

编辑于 2019-08-06 11:31:16 回复(2)

MM_Einstrin

1.

select sum(tableB.money)as 付费金额, count(tableB.userid) as 付费人数
from tableA inner join tableB
on tableA.userid=tableB.userid
and tableA.date between '2016-12-1' and '2016-12-31'
and DATEDIFF(dd,tableB.date,tableA.date)<=30
and tableB.money is not null

发表于 2018-08-16 09:31:55 回复(0)

比奇堡大厨丶

A. select count(distinct a.userid) as user_num, sum(money) as fee

from tableA a join tableB b

on a.userid = b.userid

where a.date between '2016-12-01'and '2016-12-31'

and b.date between a.date and date_add(a.date,interval 30day)

B. select (case when fee<500 then '0~500' when fee<5000 then '500~5000' else '5000~' end) as group, count(distinc userid) as user_num, sum(fee) as total_fee

from

(select userid,sum(money) as fee

from table B where date >='2016-01-01'

group by userid )M

group by (case when fee<500 then '0~500' when fee<5000 then '500~5000' else '5000~' end)

编辑于 2019-08-15 14:28:10 回复(2)

(≧∇≦)三玖

1 当打标关联小表可以先过滤大表再关联小表，也可以用小表作为主表

select count( distinct a.userid),sum(b.money)
from 
(select userid,date from tableA where date>='20161201' and date<'20170101') a
join
tableB b on a.userid=b.userid and datediff(b.date,a.date)<=30

2

select (case when money<500 then '(0,500)' 
    when money>=5000 then '[5000,max)'
    else '[500,5000)' end) 'category',
    count(distinct b.userid),sum(b.money)

from tableB b
where date>'20160101'
group by (case when money<500 then '(0,500)' when money>=5000 then '[5000,max)'
    else '[500,5000)' end)

发表于 2019-09-06 10:07:06 回复(0)

meo杨996

select

count(distinct b.uid) paycn,

sum(b.money) gmv

from (

select userid,date

from tableA

where date rlike '2016-12-%'

) a

left join tableB b

on a.userid = b.userid and date_add(a.date,interval 30 day) >= b.date

;

select

case when gmv > 0 and gmv < 500 then gmv_0_500

when gmv >= 500 and gmv < 5000 then gmv_500_5000

when gmv >= 5000 then gmv_5000

else null end as gmv_range,

count(userid) as cn,

sum(gmv) as sumgmv

from (

select

userid,

sum(money) as gmv

from tableB

where date >= '2016-01-01'

group by userid

)

group by

case when gmv > 0 and gmv < 500 then gmv_0_500

when gmv >= 500 and gmv < 5000 then gmv_500_5000

when gmv >= 5000 then gmv_5000

else null end

发表于 2023-10-10 21:23:38 回复(0)

evawan

Question B.

select cat,count(userid) as '付费人数',sum(money) as '付费金额'
from 
(select *,
        case when money<500 then '(0,500)'
             when money>=500 and money<5000 then '[500,5000)'
             when money>=5000 then '[5000,)'
        end as cat
from 
(select userid, sum(money) as 'money'
from tableB
where year(date)>=2016
group by userid ) t) t1
group by cat

发表于 2023-04-02 06:08:40 回复(0)

牛客635040129号

感觉第二题应该是这样会好一点：

WITH t1 AS(
SELECT userid, SUM(money) as sum_mon,
CASE WHEN SUM(money)>0 AND SUM(money)<500 THEN '(0-500)'
WHEN SUM(money)>=500 AND SUM(money)<5000 THEN '[500-5000)'
WHEN SUM(money)>=5000 THEN '[5000, infinity)' END AS group1
FROM tableB
WHERE date >= '2016-01-01'
GROUP BY userid)

SELECT group1, COUNT(userid) AS num_id, SUM(sum_mon) AS total_amount
FROM t1
GROUP BY group1;

发表于 2021-04-24 00:27:53 回复(0)

.201902272200286

A:

select count(distinct userid),sum(money)

from b

left join

a

on

a.userid=b.userid

where a.date>'2016-12-01' and days(b.date)-days(a.date)<30;

B:

select

case

when c.m<500 and c.m>0 then '(0,500)'

when c.m>=500 and c.m<5000 then '[500,5000)'

else '[500,~]'

end,

count(userid)

sum(m) as money

from

(select userid,sum(money) as m from b group by userid having date>='2016-01-01')

发表于 2019-08-12 15:43:44 回复(1)

Madison7

B: select count(case when 付费金额<500 then 付费金额 else null end) as (0,500)付费人数 ,

sum(case when 付费金额<500 then 付费金额 else null end) as (0,500)付费金额 from(

select userid,sum(money) as 付费金额 from tableb where year(date)>2015 group by userid) r;

发表于 2019-08-03 10:41:24 回复(0)

offer快到碗里来哇

1.

select count(userid),sum(m) from

(select userid , SUM(B.money) as m from A join B

on A.userid=B.userid and datepart(year,A.date)='2016' and datepart(month,A.date)='12' and datediff(day,A.date,B.date)<=30

group by B.userid )

2.

select(count(userid),sum(m) from

(select userid , sum( money) as m from B

where datepart(year,date)>2016

group by userid

having sum(money) between 0 and 500 )

union all

select(count(userid),sum(m) from

(select userid , sum( money) as m from B

where datepart(year,date)>2016

group by userid

having sum(money) between 500 and 5000 )

union all

select(count(userid),sum(m) from

(select userid , sum( money) as m from B

where datepart(year,date)>2016

group by userid

having sum(money) >5000)

发表于 2018-09-16 12:09:11 回复(0)

1ffer

A.SELECT COUNT(userid), SUM(user_money)
   FROM （
                 SELECT B.userid, SUM(B.money) as user_money
                 FROM B LEFT JOIN A ON A.userid = B.useid
                 GROUP BY B.userid

HAVING YEAR(A.date)>2016）as C

B.SELECT COUNT(userid), SUM(user_money)
   FROM（
               SELECT B.userid, SUM(B.money) as user_money
               FROM B LEFT JOIN A ON A.userid = B.useid
               GROUP BY B.userid
               HAVING SUM(B.money)>=500 AND SUM(B.money)<5000）as C
   UNION
   SELECT COUNT(userid), SUM(user_money)
   FROM（
               SELECT B.userid, SUM(B.money) as user_money
               FROM B LEFT JOIN A ON A.userid = B.useid
               GROUP BY B.userid
               HAVING SUM(B.money)>0 AND SUM(B.money)<500）as D
   UNION
   SELECT COUNT(userid), SUM(user_money)
   FROM（
               SELECT B.userid, SUM(B.money) as user_money
               FROM B LEFT JOIN A ON A.userid = B.useid
               GROUP BY B.userid
               HAVING SUM(B.money)>5000）as E

发表于 2018-09-01 23:51:08 回复(0)

问题信息

热门推荐

相关试题