jhon

two tables and two columns

moves:
	uid
	moveId
	ownership
	state
	moveDate

revenue:
	moveId
	revenueType
	revenue

1- how many moves occurend in calliforna (CA) during the month of 8 2018
	select count(distint (moveId)) from moves where moveDate>='2018-08-01' and  moveDate<='2018-08-31' and lowercase(state)='ca'

2 how many users have moved more than once in a year 
	select count(uid) as user from moves where user > 1 and YEAR(moveDate)='2019' 
	moves:
	uid   	1 1 1 2
 	moveId  1 2 3 1
	ownership
	state
	moveDate

3- revenue by ownership by and state
	select SUM(revenue), state, ownership from revenue INNER JOIN moves on revenue.moveId=moves.moveId group by state and ownership

-- 4. Calculate Revenue per paying user and revenue per user using a single query

	elect SUM(revenue) as revenue, group_contact(uid) as allpayingusers from revenue INNER JOIN moves on revenue.moveId=moves.moveId

	select * paying.revenue, paying.allpayingusers, FROM 
		(select SUM(revenue) as revenue, group_contact(uid) as allpayingusers from revenue INNER JOIN moves on revenue.moveId=moves.moveId) as paying

		(select uid as nonpayingIds from moves inner join moves on revenue.moveId=moves.moveId where revenue.moveId = NULL) as nonpaying 

5- --- 5. Moving 7-day average of moves using SQL


	select date, AVG(moveId) from moves where moveDate>=DATE-SUB(moveDate - 7) and moveDate<=moveDate

-- 6. Top 5 Revenue generating states for each revenue type

		
		select SUM(revenue) as totalRevenue, state, revenueType from revenue INNER JOIN moves on revenue.moveId=moves.moveId group by state and revenueType ASC totalRevenue

		select SUM(revenue) as totalRevenue, state, revenueType from revenue INNER JOIN moves on revenue.moveId=moves.moveId group by state and revenueType ASC totalRevenue limit, 0,5