Logo

Go on. Explore!

lvl 0 • 0 xp

lvl 1

HomeMarketDecksInterests
SQL

SQL

memorize.aimemorize.ai (lvl 286)
Star 0%
Star 0%
Star 0%
Star 0%
Star 0%
(0)
(0)
(0)
25 cards
()
Section 1

Preview this deck

What percentage of sales had (1) returns above 20% (by dollars) and (2) returns above 50% of their value?

Front

1 / 25

SQL
Star 0%
Star 0%
Star 0%
Star 0%
Star 0%

0.0

0 reviews

5
0
4
0
3
0
2
0
1
0

Active users

0

All-time users

0

Favorites

0

Last updated

6 years ago

Date created

Mar 1, 2020

Cards (25)

Section 1

(25 cards)

What percentage of sales had (1) returns above 20% (by dollars) and (2) returns above 50% of their value?

Front

SELECT sum(case when refundamount > .2 * amount then 1 else 0 end) /count(1) as pct_above_20 , sum(case when refundamount > .5 * amount then 1 else 0 end) /count(1) as pct_above_50 FROM table LEFT JOIN refunds ON pct_above_20.tid= pct_above_50.tid

Back

How many sales were completed each month?

Front

SELECT count(1) , date_trunc('month', salesdt) FROM t.table GROUP BY 2 ORDER BY 2;

Back

What is the average percentage refunded, on those transactions with refund?

Front

SELECT avg(refundamount / amount) as avg_ref_pct FROM table INNER JOIN refunds USING (tid);

Back

How many total sales are in the database?

Front

SELECT sum(1) FROM t.table

Back

Calculate which item (NAME) is the most returned, by percent of returns.

Front

SELECT item.name FROM table LEFT JOIN refunds USING(tid) LEFT JOIN item USING (itemID) GROUP BY item.name ORDER BY count(refunds.tid)::float / count(transactions.tid) DESC LIMIT 1;

Back

Identify the top 7 sales people (Name and SID) in terms of total revenue generated

Front

SELECT spname, sid FROM t.table JOIN t.othertable USING(iid) JOIN t.t USING(sid) GROUP BY 1,2 ORDER BY sum(prc) DESC LIMIT 7;

Back

How many salespeople had NO REFUNDS. We need to make sure that there are no refunds for any of the transactions for a sales person

Front

SELECT id FROM table LEFT JOIN refunds USING (TID) GROUP BY 1 HAVING count(refunds.refundamount ) = 0;

Back

For each salesperson (NAME), what percentage of their sales were refunded?

Front

SELECT name, sum(refundamount) / sum(amount) as pct_refund FROM table LEFT JOIN salesperson USING (id) LEFT JOIN refunds USING(tid) GROUP BY 1;

Back

Salespeople are paid based on one fo two plans. The H bonus plan which means that they are paid $130 per day, but receive a 10% commission or the L bonus plan which they are paid $150 per day but receive a 5% commission. Calculate the amount of money that each salesperson made on the NON COMISSION PART

Front

SELECT sid , CASE WHEN bonus = 'H' then 130.0max(daysworked) + .1sum(prc/100) ELSE 150max(daysworked) +.05sum(prc/100) END AS total_comp FROM sp.sp LEFT JOIN sp.itemlist USING(iid) GROUP BY 1;

Back

How many sales were completed by region?

Front

SELECT count(1), region FROM t.table LEFT JOIN t.othertable on t.table.name = t.othertable.name GROUP BY 2 ORDER BY 2;

Back

Calculate the total revenue from each state

Front

SELECT state, sum(amount) as sum_amt FROM table LEFT JOIN salesperson USING(id) GROUP BY 1;

Back

What was the average number of days that a salesperson worked

Front

SELECT avg( daysworked) FROM sp.sp;

Back

Calculate the average profit per region

Front

SELECT avg(prft)/100.0 AS prft FROM ( SELECT sum(prc - cost) FROM sp.sp JOIN sp.itemmap USING (sid) JOIN sp.itemlist USING (sid) GROUP BY region) AS InnerQ

Back

Calculate the percentage of revenue from each state?

Front

SELECT lhs.state, lhs.state_amt / rhs.totalsales FROM ( SELECT state, sum(amount) as state_amt FROM table LEFT JOIN salesperson USING (id) GROUP BY 1 ) as lhs CROSS JOIN (SELECT sum(amount) as total sales FROM table ) as rhs;

Back

For each month, report the percentage of sales refunded by both number of refunds and dollars. Assume that a refund can occur in any month after a sale, but that all refunds are in these tables.

Front

SELECT date_part('month', transTS) as sales_month , count( refunds.refundamount ) : :float /count(transactions.amount) as pct_dol_ref FROM table LEFT JOIN refunds USING (tid) GROUP BY 1;

Back

Which sales person(name only) had the HIGHEST PERCENTAGE of refunds, based on number of transactions.

Front

SELECT id FROM table LEFT JOIN refund ON transactions.tid = refund.tid LEFT JOIN salesperson ON transition.id = salesperson.id GROUP BY 1 ORDER BY sum(refundamount) ;

Back

Plot the monthly revenue (combined) for the top 7 salespeople

Front

SELECT sum(prc) /100.0, date_trunc('month', salesdt) as mnt FROM sp.itemmap JOIN sp.itemlist USING(iid) WHERE sid in (SELECT sid FROM sp.itemlist JOIN sp.itemmap USING(iid) JOIN sp.sp USING(sid) GROUP BY 1 ORDER BY sum( prc ) DESC LIMIT 7) GROUP BY 2 ORDER BY 2;

Back

What are the top 5 blank (id only) in terms of numbers of $?

Front

SELECT COUNT(1) as num_sales, id FROM table GROUP BY 2 ORDER BY 1 DESC LIMIT 5;

Back

Create a chart breaking down all revenue into one of four categories: (a) the sales person worked less than 10 days (b) the salesperson worked between 10 and 20 days (c) the salesperson worked between 20 and 50 days (d) salesperson worked more than 50 days

Front

SELECT sum(prc)::float/100 as rev, CASE WHEN daysworked <10 then 1 WHEN daysworked<20 then 2 WHEN daysworked<50 then 3 else 4 END FROM sp.sp JOIN sp.itemmap USING(sid) JOIN sp.itemlist USING(iid) GROUP BY 2;

Back

calculate the total revenue from all states

Front

SELECT sum(amount) as totalsales FROM table

Back

Prepare a table containing the following: Month Number of Sales for that Month Total Revenue from Sales that Month Total Cost of Items from that Month

Front

SELECT count(1) , date_trunc('month', salesdt) , sum(prc)/100.0 AS totalRev , sum(cost)/100.0 AS totalCos FROM t.table LEFT JOIN t.othertable USING (shared_name) GROUP BY 2 ORDER BY 2;

Back

What are the top 10 sales people (Name) in terms of dollars of sales?

Front

SELECT name FROM table LEFT JOIN salesperson USING(id) GROUP BY 1 ORDER BY sum(amount) DESC

Back

When do you use USING?

Front

ONLY when you're working with/joining on two columns with the same name.

Back

What are the names of the top 5 salespeople in terms of number of sales?

Front

SELECT COUNT(1) as num_sales, name FROM table LEFT JOIN salesperson USING(id) GROUP BY name ORDER BY 1 DESC LIMIT 5;

Back

Which mobile phone area code (first three digits) has the highest number of sales?

Front

SELECT left( phone_number, 3) as area_code FROM table LEFT JOIN salesperson USING(id) GROUP BY 1 ORDER BY sum(amount) DESC LIMIT 10;

Back