NXL и SQL — одни и те же вопросы

from companieswith/any deals [stage = "won"]show [name]

SELECT c.nameFROM companies cWHERE EXISTS (SELECT 1 FROM deals d              WHERE d.company_link = c.id                AND d.stage = 'won');

from enrolleeswhere [status = "enrolled"]follow centersshow/unique [label]

SELECT DISTINCT c.labelFROM enrollees eJOIN centers c ON e.center_link = c.idWHERE e.status = 'enrolled';

from categorieswith/any categories [name = "Fiction"]follow booksshow [id title]

SELECT b.id, b.titleFROM books bJOIN categories c ON b.category_link = c.idJOIN categories p ON c.parent_link = p.idWHERE p.name = 'Fiction';

from memberwith/any notice [replied = "no"]show [name grade]

SELECT m.name, m.gradeFROM member mWHERE EXISTS (SELECT 1 FROM notice n              WHERE n.member_link = m.id                AND n.replied = 'no');

from impressionswith/any placements [starts-with(category_code, "SPT")]reduce [n: count()]

SELECT COUNT(*) AS nFROM impressions iWHERE EXISTS (SELECT 1 FROM placements p              WHERE p.id = i.placement_link                AND p.category_code LIKE 'SPT%');

from mould_runwhere [starts-with(tool_ref, "T-4410/")]show [tool: take-after(tool_ref, "T-") cycle_sec]

SELECT substr(tool_ref, instr(tool_ref, 'T-') + 2) AS tool,       cycle_secFROM mould_runWHERE tool_ref LIKE 'T-4410/%';

from milestonewhere [contains(note, "[")]show [id title note tag: take-between(note, "[", "]")]

SELECT id, title, note,       substr(note, instr(note, '[') + 1,              instr(substr(note, instr(note, '[') + 1), ']') - 1) AS tagFROM milestoneWHERE note LIKE '%[%';

from vitals8follow appointment8show/unique [visit_label]

SELECT DISTINCT a.visit_labelFROM vitals8 vJOIN appointment8 a ON v.appointment8_link = a.id;

from profilewith/any swipe.liked_link [action = "like"]show/unique [gender]

SELECT DISTINCT p.genderFROM profile pWHERE EXISTS (SELECT 1 FROM swipe s              WHERE s.liked_link = p.id                AND s.action = 'like');

from inspectionswhere [is-value(comment) and comment != ""]show [id comment      len: length-of(comment)      mentions: contains(comment, "excellent")]

SELECT id, comment,       length(comment) AS len,       instr(comment, 'excellent') > 0 AS mentionsFROM inspectionsWHERE comment IS NOT NULL AND comment <> '';

from returnsfollow order_itemsfollow ordersfollow customersshow/unique [id name]

SELECT DISTINCT c.id, c.nameFROM returns rJOIN order_items oi ON r.order_item_link = oi.idJOIN orders o ON oi.order_link = o.idJOIN customers c ON o.customer_link = c.id;

from line0where [lname = "East Plant"]follow lot0follow audit0where [outcome = "fail"]follow fault0show/unique [id dname]

SELECT DISTINCT f.id, f.dnameFROM line0 lJOIN lot0 lo ON lo.line_link = l.idJOIN audit0 a ON a.lot_link = lo.idJOIN fault0 f ON a.fault_link = f.idWHERE l.lname = 'East Plant'  AND a.outcome = 'fail';

from eventwhere [resolved = false]follow unitfollow hullshow/unique [id tail]

SELECT DISTINCT h.id, h.tailFROM event eJOIN unit u ON e.unit_link = u.idJOIN hull h ON u.hull_link = h.idWHERE e.resolved = 0;

from sgwith/none tr []follow jbshow/unique [id ttl]

SELECT DISTINCT j.id, j.ttlFROM sg sJOIN jb j ON s.jb_link = j.idWHERE NOT EXISTS (SELECT 1 FROM tr t                  WHERE t.sg_link = s.id);

from f_callwith/any d_ctype [sev_rank = 1]follow d_stnshow/unique [id name]

SELECT DISTINCT s.id, s.nameFROM f_call fJOIN d_stn s ON f.stn_link = s.idWHERE EXISTS (SELECT 1 FROM d_ctype t              WHERE t.id = f.ctype_link                AND t.sev_rank = 1);

from position6with/any security6 [segment = "tech"]follow fund6show/unique [id code manager]

SELECT DISTINCT f.id, f.code, f.managerFROM position6 pJOIN fund6 f ON p.fund_link = f.idWHERE EXISTS (SELECT 1 FROM security6 s              WHERE s.id = p.security_link                AND s.segment = 'tech');

from ad_groupswhere [bid_strategy = "manual"]follow campaignsfollow advertisersshow/unique [id name]

SELECT DISTINCT a.id, a.nameFROM ad_groups gJOIN campaigns c ON g.campaign_link = c.idJOIN advertisers a ON c.advertiser_link = a.idWHERE g.bid_strategy = 'manual';

from record1attach halt1attach line1group [line1.id line1.rname       band: cases [is-none(record1.lag) then "missing"                    record1.lag > 5 then "very late"                    record1.lag > 0 then "late"                    else "on time"]]reduce [n: count()]

SELECT l.id, l.rname,       CASE WHEN r.lag IS NULL THEN 'missing'            WHEN r.lag > 5 THEN 'very late'            WHEN r.lag > 0 THEN 'late'            ELSE 'on time' END AS band,       COUNT(*) AS nFROM record1 rJOIN halt1 h ON r.halt1_link = h.idJOIN line1 l ON h.line1_link = l.idGROUP BY l.id, l.rname, band;

from bedwhere [last_cleaned < now() - 30d or is-none(last_cleaned)]attach wardshow [ward.wname bed.bed_code      last_cleaned: bed.last_cleaned]

SELECT w.wname, b.bed_code, b.last_cleanedFROM bed bJOIN ward w ON b.ward_link = w.idWHERE datetime(b.last_cleaned) < datetime('now', '-30 days')   OR b.last_cleaned IS NULL;

from customersattach/left ordersgroup [customers.id customers.name]reduce [orders: count/unique(orders.id)]

SELECT c.id, c.name, COUNT(o.id) AS ordersFROM customers cLEFT JOIN orders o ON o.customer_link = c.idGROUP BY c.id, c.name;

from ordgroup [m: start-of/month(pld)]reduce [n: count/unique(id)  fee_total: sum(fee)]sort [m asc]show [m n fee_total so-far: running-sum(fee_total)]

SELECT m, n, fee_total,       SUM(fee_total) OVER (ORDER BY m) AS so_farFROM (SELECT date(pld, 'start of month') AS m,             COUNT(*) AS n, SUM(fee) AS fee_total      FROM ord      GROUP BY 1) tORDER BY m;

from nurse_roundsort [admission_id asc checked_at asc id asc]show [id admission_id checked_at temperature_c      prior_admission: prev(admission_id)      prior_temperature: prev(temperature_c)]where [admission_id = prior_admission       and is-value(prior_temperature)       and temperature_c > prior_temperature]show [id admission_id checked_at temperature_c]

SELECT id, admission_id, checked_at, temperature_cFROM (SELECT id, admission_id, checked_at, temperature_c,             LAG(temperature_c) OVER (PARTITION BY admission_id                                      ORDER BY checked_at, id) AS prior      FROM nurse_round) tWHERE temperature_c > priorORDER BY admission_id, checked_at, id;

from fact_saleattach dim_productattach dim_dategroup [m: start-of/month(dim_date.calendar_date)       cat: dim_product.category]reduce [rev: sum(fact_sale.net_amount)]pivot [by: cat cols: [electronics grocery apparel home]]sort [m asc]

SELECT date(d.calendar_date, 'start of month') AS m,       SUM(CASE WHEN p.category = 'electronics' THEN f.net_amount END) AS electronics,       SUM(CASE WHEN p.category = 'grocery' THEN f.net_amount END) AS grocery,       SUM(CASE WHEN p.category = 'apparel' THEN f.net_amount END) AS apparel,       SUM(CASE WHEN p.category = 'home' THEN f.net_amount END) AS homeFROM fact_sale fJOIN dim_product p ON f.product_link = p.idJOIN dim_date d ON f.date_link = d.idGROUP BY mORDER BY m;

from ar_invoiceswhere [is-value(header_total)]with/any customers [segment = "retail"]with/none ar_invoice_lines []show [id invoice_no header_total]

SELECT i.id, i.invoice_no, i.header_totalFROM ar_invoices iWHERE i.header_total IS NOT NULL  AND EXISTS (SELECT 1 FROM customers c              WHERE c.id = i.customer_link                AND c.segment = 'retail')  AND NOT EXISTS (SELECT 1 FROM ar_invoice_lines l                  WHERE l.invoice_link = i.id);

diff [  from inventory_counts  follow products  show/unique [sku]] [  from fact_inventory  show/unique [sku]] [sku]

SELECT COALESCE(a.sku, b.sku) AS sku,       CASE WHEN a.sku IS NULL THEN 'only-right'            ELSE 'only-left' END AS diff_statusFROM (SELECT DISTINCT p.sku      FROM inventory_counts c      JOIN products p ON c.product_link = p.id) aFULL OUTER JOIN (SELECT DISTINCT sku FROM fact_inventory) b  ON a.sku = b.skuWHERE a.sku IS NULL OR b.sku IS NULL;

diff [  from docket  show/unique [zone_code]] [  from register  follow zones  show/unique [zone_code: code]] [zone_code]show [zone_code diff_status]

SELECT COALESCE(a.zone_code, b.zone_code) AS zone_code,       CASE WHEN b.zone_code IS NULL THEN 'only-left'            ELSE 'only-right' END AS diff_statusFROM (SELECT DISTINCT zone_code FROM docket) aFULL OUTER JOIN (SELECT DISTINCT z.code AS zone_code                 FROM register r                 JOIN zones z ON r.zone_link = z.id) b  ON a.zone_code = b.zone_code COLLATE NOCASEWHERE a.zone_code IS NULL OR b.zone_code IS NULL;

diff [  from probe_calibration  follow reservoirs  show/unique [tag: name]] [  from oem_certificates  show/unique [tag: reservoir_tag]] [tag]where [diff_status = "only-right"]

SELECT DISTINCT o.reservoir_tag AS tagFROM oem_certificates oWHERE NOT EXISTS (SELECT 1                  FROM probe_calibration c                  JOIN reservoirs r ON c.reservoir_link = r.id                  WHERE r.name = o.reservoir_tag);

diff [  from order_items  attach orders  attach drugs  show/unique [order_no: orders.order_no               drug_name: drugs.name]] [  from compounds  with/any infusion_admin []  attach compound_items  show/unique [order_no: compounds.order_no               drug_name: compound_items.drug_name]] [order_no drug_name]where [diff_status = "only-right"]show [order_no drug_name]

SELECT DISTINCT c.order_no, ci.drug_nameFROM compounds cJOIN compound_items ci ON ci.compound_link = c.idWHERE EXISTS (SELECT 1 FROM infusion_admin ia              WHERE ia.compound_link = c.id)  AND NOT EXISTS (SELECT 1                  FROM order_items oi                  JOIN orders o ON oi.order_link = o.id                  JOIN drugs d ON oi.drug_link = d.id                  WHERE o.order_no = c.order_no                    AND d.name = ci.drug_name);

diff [  from approved_hours  with/any projects [status = "active"]  attach projects  group [proj: projects.id w: approved_hours.week]  reduce [hrs: sum(approved_hours.hours)]] [  from timesheet_entries  with/any projects [status = "active"]  attach projects  group [proj: projects.id         w: start-of/week(timesheet_entries.worked)]  reduce [hrs: sum(timesheet_entries.hours)]] [proj w]

WITH a AS (  SELECT ah.project_link AS proj, ah.week AS w, SUM(ah.hours) AS hrs  FROM approved_hours ah  JOIN projects p ON ah.project_link = p.id  WHERE p.status = 'active'  GROUP BY 1, 2),t AS (  SELECT te.project_link AS proj,         date(te.worked, '-6 days', 'weekday 1') AS w,         SUM(te.hours) AS hrs  FROM timesheet_entries te  JOIN projects p ON te.project_link = p.id  WHERE p.status = 'active'  GROUP BY 1, 2)SELECT COALESCE(a.proj, t.proj) AS proj,       COALESCE(a.w, t.w) AS w,       CASE WHEN t.proj IS NULL THEN 'only-left'            WHEN a.proj IS NULL THEN 'only-right'            ELSE 'differs' END AS diff_status,       a.hrs AS left_hrs, t.hrs AS right_hrsFROM aFULL OUTER JOIN t ON a.proj = t.proj AND a.w = t.wWHERE a.proj IS NULL OR t.proj IS NULL OR a.hrs <> t.hrs;

diff/all [  from journal  show [ref: doc_no        amount: round/to(amount, 2)        posted: floor/step(posted, 1m)]] [  from archive  show [ref        amount: round/to(amount, 2)        posted: floor/step(posted, 1m)]] [ref]where [diff_status = "same"]

WITH j AS (  SELECT doc_no AS ref, round(amount, 2) AS amount,         strftime('%Y-%m-%d %H:%M', posted) AS posted  FROM journal),a AS (  SELECT ref, round(amount, 2) AS amount,         strftime('%Y-%m-%d %H:%M', posted) AS posted  FROM archive)SELECT j.refFROM jJOIN a ON a.ref = j.refWHERE j.ref IN (SELECT ref FROM j GROUP BY ref HAVING COUNT(*) = 1)  AND a.ref IN (SELECT ref FROM a GROUP BY ref HAVING COUNT(*) = 1)  AND j.amount = a.amount  AND j.posted = a.posted;

diff/all [  from ledger  attach job  group [job_id: job.id w: start-of/week(ledger.worked)]  reduce [h: sum(ledger.hours)]] [  from slip  attach note  attach job  show [job_id: job.id        w: slip.week        h: to-number(slip.hours)]] [job_id w]group [diff_status]reduce [n: count()]

WITH l AS (  SELECT j.id AS job_id,         date(lg.worked, '-6 days', 'weekday 1') AS w,         SUM(lg.hours) AS h  FROM ledger lg  JOIN job j ON lg.job_link = j.id  GROUP BY 1, 2),r AS (  SELECT j.id AS job_id, s.week AS w, CAST(s.hours AS REAL) AS h  FROM slip s  JOIN note n ON s.note_link = n.id  JOIN job j ON n.job_link = j.id),lk AS (SELECT job_id, w, COUNT(*) AS c, MAX(h) AS h FROM l GROUP BY 1, 2),rk AS (SELECT job_id, w, COUNT(*) AS c, MAX(h) AS h FROM r GROUP BY 1, 2),k AS (SELECT job_id, w FROM lk UNION SELECT job_id, w FROM rk)SELECT CASE WHEN COALESCE(lk.c, 0) > 1 OR COALESCE(rk.c, 0) > 1 THEN 'duplicate'            WHEN rk.c IS NULL THEN 'only-left'            WHEN lk.c IS NULL THEN 'only-right'            WHEN lk.h = rk.h THEN 'same'            ELSE 'differs' END AS diff_status,       COUNT(*) AS nFROM kLEFT JOIN lk USING (job_id, w)LEFT JOIN rk USING (job_id, w)GROUP BY 1;

diff [  from purchase_order  with/any shipment [status = "received"]  show [po: id po_number qty: expected_qty]] [  from receipt_line  attach shipment  where [shipment.status = "received"]  attach purchase_order  group [po: purchase_order.id         po_number: purchase_order.po_number]  reduce [qty: sum(receipt_line.qty_received)]] [po precision: [qty: 2]]

WITH a AS (  SELECT po.id AS po, po.po_number, po.expected_qty AS qty  FROM purchase_order po  WHERE EXISTS (SELECT 1 FROM shipment s                WHERE s.po_link = po.id AND s.status = 'received')),b AS (  SELECT po.id AS po, po.po_number, SUM(rl.qty_received) AS qty  FROM receipt_line rl  JOIN shipment s ON rl.shipment_link = s.id  JOIN purchase_order po ON s.po_link = po.id  WHERE s.status = 'received'  GROUP BY po.id, po.po_number)SELECT COALESCE(a.po, b.po) AS po,       CASE WHEN b.po IS NULL THEN 'only-left'            WHEN a.po IS NULL THEN 'only-right'            ELSE 'differs' END AS diff_statusFROM aFULL OUTER JOIN b ON a.po = b.poWHERE a.po IS NULL OR b.po IS NULL   OR abs(a.qty - b.qty) > 2   OR a.po_number <> b.po_number COLLATE NOCASE;

diff/all [  from asset_register  attach departments  group [dept_id: departments.id         dept: departments.name         yr: year-of(asset_register.acquired)]  reduce [spent: sum(asset_register.cost)]] [  from department_budgets  attach departments  group [dept_id: departments.id         dept: departments.name         yr: department_budgets.year]  reduce [spent: sum(department_budgets.capex_budget)]] [dept_id yr]

WITH a AS (  SELECT d.id AS dept_id, d.name AS dept,         CAST(strftime('%Y', ar.acquired) AS INTEGER) AS yr,         SUM(ar.cost) AS spent  FROM asset_register ar  JOIN departments d ON ar.department_link = d.id  GROUP BY 1, 2, 3),b AS (  SELECT d.id AS dept_id, d.name AS dept, db.year AS yr,         SUM(db.capex_budget) AS spent  FROM department_budgets db  JOIN departments d ON db.department_link = d.id  GROUP BY 1, 2, 3)SELECT COALESCE(a.dept_id, b.dept_id) AS dept_id,       COALESCE(a.yr, b.yr) AS yr,       CASE WHEN b.dept_id IS NULL THEN 'only-left'            WHEN a.dept_id IS NULL THEN 'only-right'            WHEN a.spent = b.spent THEN 'same'            ELSE 'differs' END AS diff_status,       a.spent AS spent_actual,       b.spent AS spent_budgetFROM aFULL OUTER JOIN b ON a.dept_id = b.dept_id AND a.yr = b.yr;

from ulnattach skuattach svcattach prvgroup [provider_id: prv.id       provider: prv.name       year: year-of(uln.used_on)       m: month-of(uln.used_on)]reduce [total: sum(uln.cost)]pivot [by: m       cols: [ene: 1 feb: 2 mar: 3 abr: 4 may: 5 jun: 6 jul: 7 ago: 8 sep: 9 oct: 10 nov: 11 dic: 12]]

SELECT p.id AS provider_id, p.name AS provider,       CAST(strftime('%Y', u.used_on) AS INTEGER) AS year,       SUM(CASE WHEN strftime('%m', u.used_on) = '01' THEN u.cost END) AS ene,       SUM(CASE WHEN strftime('%m', u.used_on) = '02' THEN u.cost END) AS feb,       SUM(CASE WHEN strftime('%m', u.used_on) = '03' THEN u.cost END) AS mar,       SUM(CASE WHEN strftime('%m', u.used_on) = '04' THEN u.cost END) AS abr,       SUM(CASE WHEN strftime('%m', u.used_on) = '05' THEN u.cost END) AS may,       SUM(CASE WHEN strftime('%m', u.used_on) = '06' THEN u.cost END) AS jun,       SUM(CASE WHEN strftime('%m', u.used_on) = '07' THEN u.cost END) AS jul,       SUM(CASE WHEN strftime('%m', u.used_on) = '08' THEN u.cost END) AS ago,       SUM(CASE WHEN strftime('%m', u.used_on) = '09' THEN u.cost END) AS sep,       SUM(CASE WHEN strftime('%m', u.used_on) = '10' THEN u.cost END) AS oct,       SUM(CASE WHEN strftime('%m', u.used_on) = '11' THEN u.cost END) AS nov,       SUM(CASE WHEN strftime('%m', u.used_on) = '12' THEN u.cost END) AS dicFROM uln uJOIN sku s ON u.sku_link = s.idJOIN svc v ON s.svc_link = v.idJOIN prv p ON v.prv_link = p.idGROUP BY p.id, p.name, year;

from weight_recordattach animalattach penwhere [animal.tag_code = "NX-1001"]sort [weight_record.weighed_on asc weight_record.wtime asc weight_record.id asc]show [weight_record.weighed_on weight_record.wtime weight_record.weight_kg      gain: weight_record.weight_kg - prev(weight_record.weight_kg)      so_far: running-sum(weight_record.weight_kg)      ma: moving-avg(weight_record.weight_kg, 2)      pen: pen.name]

SELECT w.weighed_on, w.wtime, w.weight_kg,       w.weight_kg - LAG(w.weight_kg) OVER win AS gain,       SUM(w.weight_kg) OVER (win ROWS UNBOUNDED PRECEDING) AS so_far,       AVG(w.weight_kg) OVER (win ROWS 1 PRECEDING) AS ma,       p.name AS penFROM weight_record wJOIN animal a ON w.animal_link = a.idJOIN pen p ON a.pen_link = p.idWHERE a.tag_code = 'NX-1001'WINDOW win AS (ORDER BY w.weighed_on, w.wtime, w.id)ORDER BY w.weighed_on, w.wtime, w.id;

from jobattach siteattach branchwhere [not (site.spot near [center: [40.717, -73.958] radius: 8000])]show/unique [site.code branch.name]

SELECT DISTINCT s.code, b.nameFROM job jJOIN site s ON j.site_link = s.idJOIN branch b ON j.branch_link = b.idWHERE 2 * 6371008.8 * asin(sqrt(        power(sin(radians(json_extract(s.spot, '$.coordinates[1]') - 40.717) / 2), 2)      + cos(radians(40.717)) * cos(radians(json_extract(s.spot, '$.coordinates[1]')))      * power(sin(radians(json_extract(s.spot, '$.coordinates[0]') + 73.958) / 2), 2)      )) > 8000;

from sessionssort [entered asc id asc]where [not is-duplicate([plate])]show [id plate entered]

SELECT id, plate, enteredFROM (SELECT id, plate, entered,             ROW_NUMBER() OVER (PARTITION BY plate                                ORDER BY entered, id) AS rn      FROM sessions) tWHERE rn = 1ORDER BY entered, id;

from sessionssort [entered asc id asc]where [is-duplicate([plate])]show [id plate entered]

SELECT id, plate, enteredFROM (SELECT id, plate, entered,             ROW_NUMBER() OVER (PARTITION BY plate                                ORDER BY entered, id) AS rn      FROM sessions) tWHERE rn > 1ORDER BY entered, id;

from sessionssort [entered asc id asc]where [is-duplicate([plate])] delete/cascade

DELETE FROM paymentsWHERE session_link IN (  SELECT id FROM (    SELECT id, ROW_NUMBER() OVER (PARTITION BY plate                                  ORDER BY entered, id) AS rn    FROM sessions) t  WHERE rn > 1);​DELETE FROM sessionsWHERE id IN (  SELECT id FROM (    SELECT id, ROW_NUMBER() OVER (PARTITION BY plate                                  ORDER BY entered, id) AS rn    FROM sessions) t  WHERE rn > 1);