sql - Query to find top customers in countries with greatest difference in from 2 months -

-

lets theres columns: customer_id, revenue, country, date

what query show top customers highest revenue growth nov dec?

what query show top 100 customers in each country has highest growth in revenue november in december?

calculating revenue growth can using window function lag():

select customer_id, revenue, date, country,        revenue - lag(revenue,1,revenue)                       on (partition customer_id order date) growth turnover extract(month date) in (11,12)

lag(revenue,1,0.0) return revenue previous row. if there no previous row, return current row's revenue. results in growth of 0 first row each customer.

now growth column turns problem typically solved using window functions. window functions can't nested in single query, need use 2 levels of nested derived tables:

select customer_id, revenue, date, country, diff_to_previous,        dense_rank() on (order growth desc nulls last) rnk (   select customer_id, revenue, date, country          revenue - lag(revenue,1,0.0) on (partition customer_id order date) growth   turnover   extract(month date) in (11,12) ) t1

this assigns rank each row based on growth. can't use rnk alias directly in where clause , that's why use additional level of derived table.

so final statement customer highest growth is:

select * (   select customer_id, revenue, date, country, growth,           dense_rank() on (order growth desc) rnk   (     select customer_id, revenue, date, country,             revenue - lag(revenue,1,0.0) on (partition customer_id order date) growth     turnover     extract(month date) in (11,12)   ) t1 ) t2 rnk = 1;

to 100 highest growth each country need change calculation of rnk per country:

select * (   select customer_id, revenue, date, country, growth          dense_rank() on (partition country order growth desc) rnk   (     select customer_id, revenue, date, country,             revenue - lag(revenue,1,0.0) on (partition customer_id order date) growth     turnover     extract(month date) in (11,12)   ) t1 ) t2 rnk <= 100;

date horrible name column. not because keyword more importantly not document column contains. "start date"? "end date"? "purchase date"? "due date"?


Comments

Post a Comment

Popular posts from this blog

wordpress - (T_ENDFOREACH) php error -

-
creating wordpress website , i'm getting following php error: parse error: syntax error, unexpected 'endforeach' (t_endforeach) in /home/soralmedia/public_html/wp-content/themes/soral media/woocommerce/myaccount/my-orders.php on line 98 here's code file: <?php /** * orders * * shows recent orders on account page. * * template can overridden copying yourtheme/woocommerce/myaccount/my-orders.php. * * however, on occasion woocommerce need update template files , (the theme developer). * need copy new files theme maintain compatibility. try this. * little possible, happen. when occurs version of template file will. * bumped , readme list important changes. * * @see http://docs.woothemes.com/document/template-structure/ * @author woothemes * @package woocommerce/templates * @version 2.5.0 */ if ( ! defined( 'abspath' ) ) { exit; } $my_orders_columns = apply_filters( 'woocommerce_my_account_my_orders_columns', array( ...
Read more

Export Excel workseet into txt file using vba - (text and numbers with formulas) -

-
i new vba have developed code use in excel move text/numbers txt file. issue have 3 functions need go same txt file. when run first function has set fs = createobject("scripting.filesystemobject") set = fs.createtextfile(module1.nys45uploadfilename, true) a.writeline (str) a.close. the other 2 functions had save. set fs = createobject("scripting.filesystemobject") set = fs.savetextfile(module1.nys45uploadfilename, true) a.writeline (str) a.close. the first function go txt file, other 2 error object doesn't support property or method. when changed 3 create last function goes txt file. can not figure out word use in order make other 2 functions follow txt file in order keyed. the entire code follows private sub addrecord_click() on error goto errhandler module1.nys45uploadfilename = application.getsaveasfilename(filefilter:="textfiles (*.txt), *.txt") if module1.nys45uploadfilename = "false" exit sub ...
Read more

Using django-mptt to get only the categories that have items -

-
sample structure: all uppercase: category mixed case: item root ├── books │   ├── fiction │   │   └── classics │   └── non-fiction ├── clothing └── electronics ├── laptops ├── phones │   ├── apple │   │   ├── iphone 6 │   │   ├── iphone 6 plus │   │   ├── iphone 6s │   │   └── iphone 6s plus │   ├── motorola │   │   ├── moto g4 │   │   ├── moto g4 play │   │   ├── moto g4 plus │   │   └── moto x │   └── samsung └── tablets └── apple i'm trying have index page show "electronics" category. "clothing" category shouldn't show since it's empty , "books" category shouldn't show either since, although has child categories, child categories don't have items. similarly, "electronics" page should show "phones" category. "laptops" category shouldn't show since it's empty , "tablets" category shouldn't show either since...
Read more