Forum OpenACS Q&A: Calculation of Top Seller Products in ecommerce module

Hi, is anybody have an idea for a calculation of top seller products in Openacs 3.2.5. What i'm trying to do is this; calculate how many same product had been bought from ec_items counting product_id nightly using ecommerce-scheduled-procs.tcl and insert top 10 selected products into ec_product_top_sellers. I'll appreciate your any ideas and suggestions. Thanks for your cooperation.

## Procedure for Top Sellers (Top 10)
## I'd like to run this calculations of product top sellers nightly.
proc ec_calculate_product_top_sellers {} {
    set db_pools [ns_db gethandle subquery 2]
    set db [lindex $db_pools 0]
    set db_sub [lindex $db_pools 1]
    
    set selection [ns_db select $db "select product_id from 
ec_products where search_keywords is not null"]

    set sub_selection [ns_db select $db_sub "select i.product_id as 
correlated_product_id, count(*) as n_product_occurrences
	from ec_items i
	group by i.product_id
	order by n_product_occurrences desc"]

	set correlated_product_counter 0
	set insert_cols [list]
	set insert_vals [list]
	while { [ns_db getrow $db_sub $sub_selection] } {
	    set_variables_after_subquery

	    ## PGsql 6.x hack for group by (BMA)
	    if {$product_id == ""} {
		continue
	    }

	    if { $correlated_product_counter >= 9 } {
		ns_db flush $db_sub
		break
	    }
	    lappend insert_cols "top_product_$correlated_product_counter"
	    lappend insert_vals $correlated_product_id
	    incr correlated_product_counter
	}

	if { [llength $insert_cols] > 0 } {
		ns_db dml $db_sub "insert into ec_product_top_sellers
		([join $insert_cols ", "])
		values
		([join $insert_vals ", "])
		"
	}
    ns_db releasehandle $db
    ns_db releasehandle $db_sub
}


## Schedule this procedure

ns_share -init {set ec_procs_scheduled_p 0} ec_procs_scheduled_p

if { !$ec_procs_scheduled_p  && [ad_parameter EnabledP ecommerce 0]} 
{
    set ec_procs_scheduled_p 1

    # Scheduled proc scheduling:

    # Nightly
    ns_log Notice "scheduling ec_calculate_product_top_sellers"
    ns_schedule_daily -thread 4 14 ec_calculate_product_top_sellers

}



## Date Table of ec_product_top_sellers is like this.