banned users

function processSQLFile(fileName) {

  // Extract SQL queries from files. Assumes no ';' in the fileNames
  var queries = fs.readFileSync(fileName).toString()
    .replace(/(\r\n|\n|\r)/gm," ") // remove newlines
    .replace(/\s+/g, ' ') // excess white space
    .split(";") // split into all statements
    .map(Function.prototype.call, String.prototype.trim)
    .filter(function(el) {return el.length != 0}); // remove any empty ones

  // Execute each SQL query sequentially
  queries.forEach(function(query) {
    batch.push(function(done) {
      if (query.indexOf("COPY") === 0) { // COPY - needs special treatment
        var regexp = /COPY\ (.*)\ FROM\ (.*)\ DELIMITERS/gmi;
        var matches = regexp.exec(query);
        var table = matches[1];
        var fileName = matches[2];
        var copyString = "COPY " + table + " FROM STDIN DELIMITERS ',' CSV HEADER";
        var stream = client.copyFrom(copyString);
        stream.on('close', function () {
          done();
        });
        var csvFile = __dirname + '/' + fileName;
        var str = fs.readFileSync(csvFile);
        stream.write(str);
        stream.end();
      } else { // Other queries don't need special treatment
        client.query(query, function(result) {
          done();
        });
      }
    });
  });
}

select * 
from folder f
  join uploads u ON u.id = f.folderId 
where '8' = ANY (string_to_array(some_column,','))

DECLARE @AnyDate DATETIME
SET @AnyDate = GETDATE()

SELECT @AnyDate AS 'Input Date',
  DATEADD(q, DATEDIFF(q, 0, @AnyDate), 0) 
                        AS 'Quarter Start Date',       
  DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, @AnyDate) + 1, 0)) 
                        AS 'Quarter End Date'

with data as (
select
    date(created_at) as date,
    sum(if(app_promoting_co = 'Reby' and owner_co != 'Reby',1,0)) as hey,
    count(*) as all_
from `reby-cloud.analytics_reby_v1_eu.transactions_combined_reassigned_final`
where type like 'ride%'
    and date(created_at) > current_date - 50
    and service_area = 'Barcelona'
group by 1
--order by 1 desc
)
select hey / all_ from data order by 1 desc

select
    money_in,
    money_out,
    count(*),
    sum(amount)
from (
select
 amount,
 date(date_ts) as date,
 case
    when amount between 0 and 20 then "0-20"
    when amount between 21 and 500 then "20-500"
    when amount between 21 and 500 then "500-1000"
    when amount >1000 then "+1000"
    else "other"
end as money_in,
case
    when amount between -20 and 0 then "0-20"
    when amount between -500 and -1000 then "20-500"
    when amount between -1000 and -500 then "500-1000"
    when amount <-1000 then "+1000"
    else "other"
end as money_out,

from `reby-cloud.reby_fin_eu.payments_all_platforms`
where
  date(date_ts) BETWEEN '2021-04-01' and '2021-06-30'
  and payment_provider = 'kernel'
--order by date_ts desc
)
group by 1,2

SELECT
    id,
    stats[offset(0)].total_rides,
    mv.service_area
FROM
  `reby-cloud.analytics_reby_v1_eu.pg_users_json` u
left join `reby-cloud.reby_marketing_eu.users_servicearea_first_mv_ride` mv on u.id = mv.user_id
where date(created_at) = current_date -2
order by 2 desc
;

--with application_opened as (
select
    original_timestamp,
    anonymous_id,
    context_ip,
    context_network_carrier,
    context_os_name,
    context_os_version,
    user_id
from(
SELECT
    original_timestamp,
    anonymous_id,
    context_ip,
    context_network_carrier,
    context_os_name,
    context_os_version,
    user_id,
    row_number() over (partition by anonymous_id order by original_timestamp asc) as r_n
FROM
  `reby-safir.react_native.install_attributed`
WHERE
  context_app_name = 'Reby'
  --and user_id = 'usr_3t8h6t8c4ppubjp9vjvh'
  and anonymous_id = '1eafe4e3-f4d8-416d-80c1-db0a95191b55'
  /*
  and _PARTITIONTIME BETWEEN TIMESTAMP_TRUNC(TIMESTAMP_MICROS(UNIX_MICROS(CURRENT_TIMESTAMP()) - 60 * 60 * 60 * 24 * 1000000), DAY, 'UTC')
					AND TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY, 'UTC')
    
			
    */
    )
--where r_n = 1
order by r_n asc
--)
;
select *
from `reby-safir.react_native.identifies`
where user_id = 'usr_3t8cxanx6kehxg38n5m1'
order by original_timestamp asc
;
select *
from `reby-safir.react_native.finish_ride`
where user_id = 'usr_3t8ethqfzs32sp1mcu5h'
order by original_timestamp asc
;

with ads_data as (
SELECT
    ad_id,
    adset_id,
    campaign_id,
    status,
    sum(impressions) clicks,
    sum(spend) as investment
FROM `reby-cloud.facebook_ads.insights` ai
left join `reby-cloud.facebook_ads.ads` ads on ai.ad_id = ads.id

--WHERE DATE(_PARTITIONTIME) = "2021-07-06"
group by 1,2,3,4
)
select * from ads_data where ad_id = '23843560033720454' order by clicks desc

--'usr_3t7nh9pkvax1j6412nv1'
select * from (
with promotions as (
SELECT * FROM EXTERNAL_QUERY("reby-cloud.eu.reby_prod", 
'''
SELECT
    id_to_time(id) as created_at,
    concat('acc_',id_to_text(account_id)) as account_id,
    balance__amount as balance_amount,
    balance__currency as balance_currency,
    concat('c_',id_to_text(company_id)) as company_id,
    free_unlocks,
    concat('cpua_',id_to_text(id)) as id,
    concat('tx_',id_to_text(last_promotion_transaction_id)) as transaction_id,
    concat('usr_',id_to_text(user_id)) as user_id
FROM company_user_promotion_account
--where user_id = id_from_text('3t7nh9pkvax1j6412nv1')
;
'''
    )
)
select
    *
from (
    SELECT
    --promotions.balance_amount,
    sa.service_area,
    cp.code,
    cpu.*
    FROM
    `reby-cloud.analytics_reby_v1_eu.pg_company_promotion_usage` cpu
    left join `reby-cloud.analytics_reby_v1_eu.pg_company_promotion` cp on cpu.company_promotion_id = cp.id
    left join `reby-cloud.analytics_reby_v1_eu.reby_users_servicearea` sa on sa.user_id = cpu.user_id
    --left join promotions on cpu.user_id =promotions.user_id
    --where sa.service_area in ('Zaragoza','Gijón')
    --and date(expiration_date) > current_date and date(cpu.created_at) > current_date -15
    --where cpu.user_id = 'usr_3t7nh9pkvax1j6412nv1'
    --order by cpu.created_at desc
)
where
    (service_area = 'Zaragoza' AND code in ('SEVILLAES','REBYBCN','REBYJON','REBYGONA','SEVILLAES','TOTAREBY','HOLAMOTOS')) OR
    (service_area = 'Sevilla' AND code in ('REBYBCN','REBYJON','REBYGONA','TOTAREBY','HOLAMOTOS','REBYGO')) OR
    (service_area = 'Gijón' AND code in ('SEVILLAES','REBYBCN','REBYGONA','SEVILLAES','TOTAREBY','HOLAMOTOS')) OR
    (service_area = 'Terrassa' AND code in ('SEVILLAES','REBYBCN','REBYGONA','SEVILLAES')) OR
    (service_area in ('Napoles','Lecce','Minturno','Bergamo','Grosseto','Caserta') AND code in ('SEVILLAES','REBYBCN','REBYJON','REBYGONA','SEVILLAES','REBYGO','HOLAMOTOS'))
)
--where user_id = 'usr_3t7nh9pkvax1j6412nv1'
where value_remaining_amount > 0
and is_expired is false

INSERT `reby-cloud.analytics_reby_v1_eu.service_area_pricing_daily`
    (date, name, company_id,service_area_id,vehicle_type,price_per_minute,
    price_base_amount,original_price_per_minute,original_price_base_amount)
SELECT date('2021-07-17'), name, company_id,service_area_id,vehicle_type,price_per_minute,
    price_base_amount,original_price_per_minute,original_price_base_amount
FROM `reby-cloud.analytics_reby_v1_eu.service_area_pricing_daily`
where date = '2021-07-18'

WHERE DATE(cat_tbl.date)
    BETWEEN PARSE_DATE('%Y%m%d', @DS_START_DATE) AND
    PARSE_DATE('%Y%m%d', @DS_END_DATE)

create or replace table `temp_eu.delete_gerardm_rcnp_` as (
select npr_rscb = RCnp as bool,* from (
SELECT 
        --vd.*,
        r.created_at,
        r.user_id,
        r.id as ride_id,
        SUM(rc.theoretical_cost)/100 AS RC, 
        SUM(rc.non_promoted)/100 AS RCnp, 
        SUM(rc.theoretical_cost-rc.non_promoted)/100 AS RCp,
        --ops_managers_dashboard.TRUNC_DATE_ISO('DAY', date(datetime(r.created_at,"Europe/Madrid"))) as week_at 
    FROM`reby-cloud.analytics_reby_v1_eu.pg_rides` AS r
    LEFT JOIN `reby-cloud.analytics_reby_v1_eu.vehicle_daily` AS vd
        ON r.vehicle_id = vd.vehicle_id AND date(datetime(r.created_at,"Europe/Madrid")) = vd.date
    LEFT JOIN `reby-cloud.analytics_reby_v1_eu.py_ridestatus_combined` AS rc
        ON r.id = rc.ride_id
    LEFT JOIN `reby-cloud.analytics_reby_v1_eu.pg_vehicles` AS v
        ON r.vehicle_id = v.id
    LEFT JOIN `reby-cloud.analytics_reby_v1_eu.pg_service_area` AS sa
        ON r.service_area_id = sa.service_area_id
    LEFT JOIN `reby-cloud.analytics_reby_v1_eu.pg_tpl` AS tpl
        ON vd.tpl_provider_id = tpl.id
   -- WHERE 
        --date(datetime(r.created_at,"Europe/Madrid")) >= ops_managers_dashboard.DATE_AGO_MANAGERS_ISO('DAY')
        --AND ST_DISTANCE (ST_GEOGPOINT(r.longitude_initial, r.latitude_initial), ST_GEOGPOINT(tpl.longitude, tpl.latitude)) > 200
        --AND ST_WITHIN(ST_GEOGPOINT(r.longitude_initial, r.latitude_initial), ST_GeogFromText(sa.geometry))
        --AND v.printed_code IS NOT NULL AND vd.date >= ops_managers_dashboard.DATE_AGO_MANAGERS_ISO('DAY')
        --AND rc.ride_id IS NOT NULL
        --where r.id = 'r_3rytmr4br7g5qlu4jbqh'
        group by 1,2,3
    --GROUP BY week_at 
    --ORDER BY week_at DESC
) as a--where ride_id = 'r_3t26l79xmu2xa7nyu3n1'
full join (select ride_id ride_id_rscb,service_area, sum(non_promoted)/100 as npr_rscb
            from `reby-cloud.analytics_reby_v1_eu.py_ridestatus_combined` group by 1,2) b
    on a.ride_id = b.ride_id_rscb
)

/*
create or replace table `reby-cloud.analytics_reby_v1_eu.vehicle_daily` 
PARTITION BY date
CLUSTER BY service_area_id,company_id
AS
select * except(r_n) from (
select
    *,
    row_number() over (partition by date,vehicle_id order by created_at asc) as r_n
from `reby-cloud.analytics_reby_v1_eu.vehicle_daily`
) where r_n = 1
*/

--Creación de usuarixs nuevxs
with base_data as (
select 
    date(u.created_at) as date,
    date_trunc(date(u.created_at),MONTH) as month,
    mv.service_area,
    sum(if(mv.first_ride_date is not null,1,0)) as has_ride,
    count(*) users_created
from `reby-cloud.analytics_reby_v1_eu.pg_users_json` u
left join `reby-cloud.reby_marketing_eu.users_servicearea_first_mv_ride` mv
    on u.id = mv.user_id
where mv.service_area = 'Sevilla'
group by 1,2,3
--order by 1 desc
)
select 
    *
    --lag(users_created) over (partition by month order by month asc) as prev_month_users
from base_data
order by date desc
;
--métricas de viajes individuales
select
    date(created_at) as date,
    r.id as ride_id,
    r.minutes,
    extract(hour from datetime(created_at,"Europe/Madrid")) as hour,
    if(plan_usage_id is null,0,1) as plan_usage,
    FORMAT_DATE('%A', date(created_at)) AS weekday_name,
    r.distance/100 as distance_km,
    r.ride_cost_time as ride_cost,
    geo.Distri_11D as distrito_inicial,
    geo2.Distri_11D as distrito_final,
from analytics_reby_v1_eu.pg_rides r
join `reby-cloud.analytics_reby_v1_eu.geo_sevilla_distritos` geo
    on st_within(st_geogpoint(r.longitude_initial,r.latitude_initial),geo.geometry)
join `reby-cloud.analytics_reby_v1_eu.geo_sevilla_distritos` geo2
    on st_within(st_geogpoint(r.longitude_final,r.latitude_final),geo2.geometry)
where r.service_area_id = 'sa_3qr9213ajv94b6v49h5h'
order by 1 desc
;
--ride linestrings
with rides_data as (
select
	id,
  created_at,
  starting.latitude as lat_init,
  starting.longitude as long_init,
  ending.latitude as lat_fin,
  ending.longitude as long_fin,
  distance/1000 as dist_km,
  minutes,
	--CONCAT(starting.latitude,',',starting.longitude) AS marker_start,
	--CONCAT(ending.latitude,',',ending.longitude) AS marker_end
from (
select
  r.created_at,
  r.minutes,
  r.distance,
  r.id,
  --path,
  path[offset(0)] as starting,
  path[offset(array_length(path)-1)] as ending,
  --array_length(path)
from `analytics_reby_v1_eu.pg_rides_json` r
left join `reby-cloud.analytics_reby_v1_eu.pg_vehicles` v
  on r.vehicle_id = v.id
where array_length(path) > 3
and date(r.created_at) >= '2021-06-01'
and date(r.created_at) <= '2021-09-01'
and r.service_area_id = 'sa_3qr9213ajv94b6v49h5h'
--and v.vehicle_type = 'motorcycle'
--where id = 'r_3qm5ua4jymv1ta3tbmq1'
--group by 1
  )
),

linestrings as (
select 
  ri.id,
  --st_asgeojson(st_geogpoint(rd.long_init,rd.lat_init)) as starting_point,
  concat("Linestring(",string_agg(concat(p.longitude," ",p.latitude), ","),")") as path
from `analytics_reby_v1_eu.pg_rides_json` ri, unnest(path) as p
where id in (select id from rides_data)
and date(created_at) >= '2021-06-01'
and date(created_at) <= '2021-09-01'
group by 1
),

linestrings_geo as (
select 
  rd.id,
  st_asgeojson(st_geogpoint(rd.long_init,rd.lat_init)) as starting_point,
  st_asgeojson(st_geogpoint(rd.long_fin,rd.lat_fin)) as ending_point,
  st_asgeojson(SAFE.st_geogfromtext(path)) as paths
from linestrings ls
join rides_data rd on ls.id = rd.id
)

select * from linestrings_geo

--usuarios con varias tarjetas
SELECT concat('https://admin.reby.tech/riders/usr_', id_to_text(ppg.user_id)),
       count(distinct(card__fingerprint))
FROM payment_processor_gateway ppg
LEFT JOIN banned_device bd ON ppg.user_id=bd.user_id
where reason is null
GROUP BY 1
having count(distinct(card__fingerprint))>5
ORDER BY 2 DESC
LIMIT 100;


--tarjetas utilizadas por varias cuentas
SELECT --concat('https://admin.reby.tech/riders/usr_', id_to_text(ppg.user_id)),
card__fingerprint,
card__last_four,
       count(distinct(ppg.user_id))
FROM payment_processor_gateway ppg
left join public.user on public.user.id=ppg.user_id
LEFT JOIN banned_device bd ON ppg.user_id=bd.user_id
where reason is null and card__fingerprint is not null
GROUP BY 1,2
having count(distinct(ppg.user_id))>3
ORDER BY 3 DESC;

--usuarios que utilizan la tarjeta con código que introduzcáis:
SELECT 
distinct(concat('https://admin.reby.tech/riders/usr_', id_to_text(ppg.user_id))),
--distinct(ppg.user_id),
card__fingerprint
FROM payment_processor_gateway ppg
LEFT JOIN banned_device bd ON ppg.user_id=bd.user_id
where reason is null and card__fingerprint='+7qy4w/5VsCCCtwf'   
ORDER BY 1 DESC
LIMIT 100


--usuarios que han utilizado tarjetas que se han utilizado en más de 5 cuentas
with data as (SELECT --concat('https://admin.reby.tech/riders/usr_', id_to_text(ppg.user_id)),
card__fingerprint,
       count(distinct(ppg.user_id))
FROM payment_processor_gateway ppg
left join public.user on public.user.id=ppg.user_id
LEFT JOIN banned_device bd ON ppg.user_id=bd.user_id
where reason is null and card__fingerprint is not null and public.user.is_staff is false and public.user.is_deleted=false
GROUP BY 1
having count(distinct(ppg.user_id))>3
ORDER BY 2 DESC)
select
distinct(public.user.phone_number),
public.user.first_name,
public.user.last_name
--data.card__fingerprint
--distinct(ppg.user_id),
FROM data 
left join payment_processor_gateway ppg on ppg.card__fingerprint=data.card__fingerprint 
left join public.user on public.user.id=ppg.user_id
and public.user.is_staff is false and public.user.phone_number is not null
--order by data.card__fingerprint;

--usuarios con dispositivos en los que se han registrado más de 5 cuentas
with data as (SELECT hash_device,
       count(hash_device) as number_accounts
FROM user_device ud
GROUP BY 1
HAVING count(hash_device)>=5
ORDER BY 2 desc)
SELECT distinct(public.user.phone_number),
public.user.first_name,
       public.user.last_name,
       public.user.phone_number,
       --data.hash_device,
       service_area.name,
       user_last_location.last_location_at
FROM DATA
LEFT JOIN user_device ud ON ud.hash_device=data.hash_device
LEFT JOIN public.user ON public.user.id=ud.user_id
AND public.user.is_staff IS FALSE
AND public.user.phone_number IS NOT NULL
and public.user.is_deleted=false
left join user_last_location on public.user.id=user_last_location.user_id 
left join service_area on user_last_location.last_service_area_id = service_area.id
where service_area.name='Terrassa'
ORDER BY user_last_location.last_location_at desc
;

--Análisis usuario Alba
SELECT 
distinct(concat('https://admin.reby.tech/riders/usr_', id_to_text(ppg.user_id))),
--distinct(ppg.user_id),
card__fingerprint,
card__last_four
FROM payment_processor_gateway ppg
LEFT JOIN banned_device bd ON ppg.user_id=bd.user_id
where /* reason is null and*/ card__last_four in('1058','9219')  
ORDER BY 1 DESC
LIMIT 100

--usuarios que se han registrado en más de 5 dispositivos y balance negativo
with data as (SELECT id_to_text(user_id) as user_id,
       count(hash_device) as number_devices
FROM user_device ud
GROUP BY 1
HAVING count(hash_device)>=5
ORDER BY 2 desc)
SELECT public.user.first_name,
       public.user.last_name,
       public.user.phone_number,
       data.user_id,
       data.number_devices,
       service_area.name,
       user_last_location.last_location_at,
       account.balance__amount
FROM DATA
LEFT JOIN public.user ON id_to_text(public.user.id)=data.user_id
AND public.user.is_staff IS FALSE
AND public.user.phone_number IS NOT NULL
and public.user.is_deleted=false
left join user_last_location on public.user.id=user_last_location.user_id 
left join service_area on user_last_location.last_service_area_id = service_area.id
left join account on public.user.balance_account_id=account.id
where service_area.name='Terrassa' and account.balance__amount<0
ORDER BY user_last_location.last_location_at desc
;

--usuarios con varias tarjetas
SELECT concat('https://admin.reby.tech/riders/usr_', id_to_text(ppg.user_id)),
       count(distinct(card__fingerprint))
FROM payment_processor_gateway ppg
LEFT JOIN banned_device bd ON ppg.user_id=bd.user_id
where reason is null
GROUP BY 1
having count(distinct(card__fingerprint))>5
ORDER BY 2 DESC
LIMIT 100;


--tarjetas utilizadas por varias cuentas
SELECT --concat('https://admin.reby.tech/riders/usr_', id_to_text(ppg.user_id)),
card__fingerprint,
card__last_four,
       count(distinct(ppg.user_id))
FROM payment_processor_gateway ppg
left join public.user on public.user.id=ppg.user_id
LEFT JOIN banned_device bd ON ppg.user_id=bd.user_id
where reason is null and card__fingerprint is not null
GROUP BY 1,2
having count(distinct(ppg.user_id))>3
ORDER BY 3 DESC;

--usuarios que utilizan la tarjeta con código que introduzcáis:
SELECT 
distinct(concat('https://admin.reby.tech/riders/usr_', id_to_text(ppg.user_id))),
--distinct(ppg.user_id),
card__fingerprint
FROM payment_processor_gateway ppg
LEFT JOIN banned_device bd ON ppg.user_id=bd.user_id
where reason is null and card__fingerprint='+7qy4w/5VsCCCtwf'   
ORDER BY 1 DESC
LIMIT 100


--usuarios que han utilizado tarjetas que se han utilizado en más de 5 cuentas
with data as (SELECT --concat('https://admin.reby.tech/riders/usr_', id_to_text(ppg.user_id)),
card__fingerprint,
       count(distinct(ppg.user_id))
FROM payment_processor_gateway ppg
left join public.user on public.user.id=ppg.user_id
LEFT JOIN banned_device bd ON ppg.user_id=bd.user_id
where reason is null and card__fingerprint is not null and public.user.is_staff is false and public.user.is_deleted=false
GROUP BY 1
having count(distinct(ppg.user_id))>3
ORDER BY 2 DESC)
select
distinct(public.user.phone_number),
public.user.first_name,
public.user.last_name
--data.card__fingerprint
--distinct(ppg.user_id),
FROM data 
left join payment_processor_gateway ppg on ppg.card__fingerprint=data.card__fingerprint 
left join public.user on public.user.id=ppg.user_id
and public.user.is_staff is false and public.user.phone_number is not null
--order by data.card__fingerprint;

--usuarios con dispositivos en los que se han registrado más de 5 cuentas
with data as (SELECT hash_device,
       count(hash_device) as number_accounts
FROM user_device ud
GROUP BY 1
HAVING count(hash_device)>=5
ORDER BY 2 desc)
SELECT distinct(public.user.phone_number),
public.user.first_name,
       public.user.last_name,
       public.user.phone_number,
       --data.hash_device,
       service_area.name,
       user_last_location.last_location_at
FROM DATA
LEFT JOIN user_device ud ON ud.hash_device=data.hash_device
LEFT JOIN public.user ON public.user.id=ud.user_id
AND public.user.is_staff IS FALSE
AND public.user.phone_number IS NOT NULL
and public.user.is_deleted=false
left join user_last_location on public.user.id=user_last_location.user_id 
left join service_area on user_last_location.last_service_area_id = service_area.id
where service_area.name='Terrassa'
ORDER BY user_last_location.last_location_at desc
;

--Análisis usuario Alba
SELECT 
distinct(concat('https://admin.reby.tech/riders/usr_', id_to_text(ppg.user_id))),
--distinct(ppg.user_id),
card__fingerprint,
card__last_four
FROM payment_processor_gateway ppg
LEFT JOIN banned_device bd ON ppg.user_id=bd.user_id
where /* reason is null and*/ card__last_four in('1058','9219')  
ORDER BY 1 DESC
LIMIT 100

--usuarios que se han registrado en más de 5 dispositivos y balance negativo
with data as (SELECT id_to_text(user_id) as user_id,
       count(hash_device) as number_devices
FROM user_device ud
GROUP BY 1
HAVING count(hash_device)>=5
ORDER BY 2 desc)
SELECT public.user.first_name,
       public.user.last_name,
       public.user.phone_number,
       data.user_id,
       data.number_devices,
       service_area.name,
       user_last_location.last_location_at,
       account.balance__amount
FROM DATA
LEFT JOIN public.user ON id_to_text(public.user.id)=data.user_id
AND public.user.is_staff IS FALSE
AND public.user.phone_number IS NOT NULL
and public.user.is_deleted=false
left join user_last_location on public.user.id=user_last_location.user_id 
left join service_area on user_last_location.last_service_area_id = service_area.id
left join account on public.user.balance_account_id=account.id
where service_area.name='Terrassa' and account.balance__amount<0
ORDER BY user_last_location.last_location_at desc
;